SQL help needed ...

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

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

Code:

---

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

---

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