CODEWITHSUNDEEP
sqlt-sqlselect
Felippe Tabachi da Cruz
Felippe Tabachi da Cruz

Reputation: 11

SELECT rows containing latest values

How do I SELECT the column1 registries that have the column2 with the latest date and is not null?

For example, I need to return just the line five (employee3).

enter image description here

Upvotes: 0

Views: 73

Answers (2)

xQbert
xQbert

Reputation: 35343

Updated with a re-interpretation of the question:

I think you mean:

Return the most recent resignation date for all employees who are currently Resigned. Currently resigned is defined as "having all the same employee records with a resignation date populated for that employee". A single employee record with a NULL resignation date means the employee is still employed; regardless of how many times they have resigned!

This can be accomplished with an exists using a correlated subquery. and a max along with a group by

First we get a list of all the employees who are not resigned. Then we compare our full set to the set of employees who are not resigned and only keep employees are not in the list of employees not resigned, next we group by employee and get the max resignation.

SELECT Employee, Max(Resignation) Resignation
FROM Table A
WHERE NOT EXISTS (SELECT 1 
                  FROM Table B
                  WHERE A.Employee = B.Employee
                    and B.Resignation is null)  --Cooelation occurs here Notice how A
GROUP BY Employee

Cooelation occurs on the line WHERE A.Employee = B.Employee as A.EMPLOYEE refers to a table one level removed TABLE A from the table B.

Pretty sure there would be a way to do this with an apply join as well; but I'm not as familiar with that syntax yet.

Upvotes: 0

O. Jones
O. Jones

Reputation: 108839

How about this?

SELECT Employee, MAX(Resignation) Resignation
  FROM table
 WHERE Resignation IS NOT NULL
 GROUP BY Employee

Or, if your table has more columns than you've shown,

 SELECT a.*
   FROM table a
   JOIN (
              SELECT Employee, MAX(Resignation) Resignation
                FROM table
               WHERE Resignation IS NOT NULL
               GROUP BY Employee
        ) b ON a.Employee = b.Employee AND a.Resigation = b.Resignation

This is the "find detail rows with extreme values" query pattern.

Upvotes: 2

Related Questions