Use SQL to exclude rows with no change in specific column from query-CodePudding

How can I exclude the yellow highlighted rows? The requirement of the query is to return only rows where the job title changed, but the raw data includes extraneous rows with different effective dates which we want to be able to automatically exclude with SQL.

This is because the source system has a record effective date column that is common to several columns. We cannot change this architecture and so need the ability to exclude records from the output.

CodePudding user response：

select
a.*
FROM 
jobtitles a
LEFT JOIN jobtitles b
ON a.id = b.id AND
a.effdate < b.effdate
WHERE b.id IS NULL

something like that, that would get the latest job title anyway

you might be able to use that "pseudo table" to further query

on considering your question further, how about

select
MIN(effdate) as effdate, jobtitle
FROM jobtitles
group by employeeid, jobtitle

(I'm making the assumption they don't change job titles back and forth, if so you're basically screwed, so be aware of that)

CodePudding user response：

If JobTitle of an employee does not reverted to previous job titles, use the following query:

SELECT EmployeeID, 
    Name, 
    JobTitle, 
    MAX(Name) AS Name, 
    MIN(EffectiveDate) AS EffectiveDate
FROM jobtitles
GROUP BY EmployeeID, JobTitle
ORDER BY EmployeeID ASC, EffectiveDate DESC

If JobTitle of employees can be reverted/change to title that they have already obtained in the past, use the following query:

SELECT EmployeeID, 
    Name, 
    JobTitle,
    EffectiveDate
FROM (
  SELECT 
    EmployeeID, 
    Name, 
    JobTitle,
    EffectiveDate, 
    Lead(JobTitle,1,0) OVER (Order By EmployeeID ASC, EffectiveDate DESC) AS PrevTitle
  FROM jobtitles
) AS tmp
WHERE tmp.PrevTitle != tmp.JobTitle