How to get latest ID - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How to get latest ID

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-10-08 : 03:37:39

Dear all,

I have encountred an issue and seeking help from you,

My table is as below :
ID(PK) BASE_ID DATE Name
2 6434 8/10/12 1:28 PM ABC
5 6434 8/12/12 1:28 PM ABC
4 17982 8/10/12 1:28 PM XYZ
1 47836 8/10/12 1:27 PM PQR
3 47836 8/14/12 1:28 PM PQR
6 47842 8/10/12 1:29 PM STU

I need this output
ID(PK) BASE_ID DATE NAME
3 47836 8/14/12 1:28 PM PQR
5 6434 8/12/12 1:28 PM ABC
4 17982 8/10/12 1:28 PM XYZ
6 47842 8/10/12 1:29 PM STU

Basically I need latest ID(PK), BASE_ID

Above table has approx 15 columns and more that 50,000 records.

This is an audit table actually.

Please help..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2012-10-08 : 04:26:45

Here your Query

select id, base_id,DATE, NAME from (
select row_number() over( partition by base_id order by base_id,id desc) as s_no , id, base_id,DATE, NAME
from my_table) a
where s_no=1

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-10-10 : 01:23:06

thanks senthil_nagore, logic worked for me..

quote:
Originally posted by senthil_nagore

Here your Query

select id, base_id,DATE, NAME from (
select row_number() over( partition by base_id order by base_id,id desc) as s_no , id, base_id,DATE, NAME
from my_table) a
where s_no=1

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources