SQL help needed ...

[treker]

Hi Folks,

Somewhat new to sql and any help would be appreciated.

Trying to return a unique list of dr's based on the last admit date of their patient.

The table I'm working on has multiple entries for each dr and admit dates.

table
pat#....dr#.....Admit date
1.........a........1/1/2010
2.........a........2/1/2010
3.........b........3/1/2010
4.........b........1/1/2010

Ideally I would want to get a list like below that would return
the row for the latest admit date for each dr.

List
pat#....dr#.....Admit date
2.........a........2/1/2010
3.........b........3/1/2010

I've tried subquery with 'fetch first row' but it does not work on our system.

Thank you for any help you can render.

Regards pligi

[SDas]

treker, This may or may not work on your system as each system has its' own flavor of SQL.

SELECT PAT#, DR#, ADMIT_DATE
FROM table_name A
       INNER JOIN
     (SELECT DR#, MAX(ADMIT_DATE) as ADMIT_DATE
      FROM table_name
      GROUP BY DR#
     ) AS TABB
      ON     A.DR#        = TABB.DR#
         AND A.ADMIT_DATE = TABB.ADMIT_DATE

[treker]

Thanx for the quick reply sDas, but I'm working thru the syntax.