Query help

Hello Everyone,

Please can you help me with this?

I have an audit table, so there are many id's and modified dates... I am looking to get the last updated record for each ID. This is what I have so far... "which still gives duplicate ID's"

I have see many pages about distincta and max/min... I cannot make sense of it... HELP

1Select *2FROM TabelA3WhereExists (SELECT distinct max (id)as id ,max (ModifiedDate)as ModifiedDate4FROM TabelA)5Order by id

I tried to break it down, and still I get duplicate ID's

1Select *2FROM TabelA3Where idIN (SELECT distinct id4FROM TabelA)5Order by id

Try

SELECT *FROM TableAWHERECONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDEXISTS IN (selectCONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDas [key]from TableAgroup by ID)ORDER BY ID

|||

Thanks for your reply,

I get the following error

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'EXISTS'.

|||

SQL Server 2005:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate, row_number()OVER(partitionby idorderby ModifiedDateDESC)as RowNum

FROM TableA) t

WHERE t.RowNum=1

For SQL Server 2000, you can try:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate,(SELECTcount(*)FROM TABLEA aWHERE a.id=a1.idand a1.ModifiedDate<=a.ModifiedDate)as RowNum

FROM TableA a1) t

WHERE t.RowNum=1

|||

That's why your anAll-Star!....Shot for the help... it works very well.

Thanks B

sql