SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get latest ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pushp82
Yak Posting Veteran

83 Posts

Posted - 10/08/2012 :  03:37:39  Show Profile  Reply with Quote
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..

Edited by - pushp82 on 10/08/2012 03:39:05

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 10/08/2012 :  04:26:45  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
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
Go to Top of Page

pushp82
Yak Posting Veteran

83 Posts

Posted - 10/10/2012 :  01:23:06  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000