| Author |
 Topic  |
|
|
pushp82
Yak Posting Veteran
67 Posts |
 Posted - 10/08/2012 : 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..
|
Edited by - pushp82 on 10/08/2012 03:39:05
|
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 10/08/2012 : 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
67 Posts |
Posted - 10/10/2012 : 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
|
|
|
| |
Topic |
|
|
|
How to get latest ID
