SQLTeam.com | Weblogs | Forums

SELECT MAX on DATE


#1

Hello SQL Expert,

I have a basic query:

SELECT TYPE,EMP_ID,START_DATE,GROUP
FROM EMPTBL

And here is the output when I run the above query.

What I am trying to do is to get MAX of StartDate and keep all the fields. What I need
is to get one record only which is MAX date but because of various GROUP, I am having
more than one record. How do I achieve this so I can have the output like below?

Thanks all


#2
SELECT 
    TYPE,EMP_ID,START_DATE,GROUP   
FROM 
    (
      SELECT 
        TYPE,EMP_ID,START_DATE,GROUP
        ,ROW_NUMBER()OVER(ORDER BY Start_Date DESC) AS RN
       FROM EMPTBL
     )E
WHERE
    RN = 1

#3

Thank you Stepson. You are smart!


#4

:slight_smile: Thanks