How to select latest entries in a revision table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to select latest entries in a revision table

Author

Topic

acesover
Starting Member

15 Posts

Posted - 2008-10-15 : 14:47:56

Based on the change history sample table below, how do I query to get the latest revision only (where Rev is highest) for each unique FldID? i.e. I would like to select records with ID = 003 and 005

ID Rev Data FldID
-- --- ---- ---
01 001 Joes 001
02 002 Jona 001
03 003 Jobs 001
04 001 Pets 002
05 002 Pats 002

thanks

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-10-15 : 14:55:52

create a sub-query with max(rev) group by FldID and join it with your select statement on this table.

acesover
Starting Member

15 Posts

Posted - 2008-10-15 : 19:44:50

That worked. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 03:02:22

or use ROW_NUMBER() in sql 2005

SELECT ID ,Rev, Data, FldID
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY FldID ORDER BY Rev DESC) AS Seq,*
FROM YourTable
)t
WHERE Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources