Get a latest record from a table with Identity

Question

Get a latest record from a table with Identity

ravinderm2003

SSC Journeyman

Points: 92
More actions
March 28, 2012 at 2:09 pm

#259210

Hi all,
I have a ssis package which implements scd2 on a destination table. The destination table has identity on it which generates an id everytime a new record is added.
Now I want to write a SQL query to get the distinct entries. Here I mean if a record has been gone through scd2 changes there will be a new entry and it will have a new id. I want the query to return the latest entry and not the old one, even though it has distinct identity.
Do let me know if you need any further explanation.
Thanks a lot in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Please provide table structure so we can provide some feasible answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ravinderm2003 SSC Journeyman Points: 92 More actions · Answer 2

Hi, I found the solution myself. and here it is. AccountId has the Identity on it.

SELECT AccountID = MAX(AccountID)

,SourceKeyId

FROM ods.Account A

GROUP BY SourceKeyID

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

That's good to hear

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SkyBVI SSCoach Points: 15128 More actions · Answer 4

ravinderm2003 (3/28/2012)
Hi, I found the solution myself. and here it is. AccountId has the Identity on it.
SELECT AccountID = MAX(AccountID)
,SourceKeyId
FROM ods.Account A
GROUP BY SourceKeyID

U can also get answer by using top 1* and Order by clause

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

SKYBVI (3/29/2012)
ravinderm2003 (3/28/2012)
Hi, I found the solution myself. and here it is. AccountId has the Identity on it.
SELECT AccountID = MAX(AccountID)
,SourceKeyId
FROM ods.Account A
GROUP BY SourceKeyID
U can also get answer by using top 1* and Order by clause
Regards,
Skybvi

That is guaranteed.

It looks like the OP has a situation where there are multiples for each sourcekeyid. The top 1 would not provide the correct record in this case.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ravinderm2003 SSC Journeyman Points: 92 More actions · Answer 6

You are right Jason, the TOP clause would not give me the right results.

SkyBVI SSCoach Points: 15128 More actions · Answer 7

ravinderm2003 (3/29/2012)
You are right Jason, the TOP clause would not give me the right results.

OK, maybe iam wrong, but

you should always provide DDL and some sample data ...

So that we can understand your data and help.

Regards

Skybvi

Regards
Sushant Kumar
MCTS,MCP

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

SKYBVI (3/29/2012)
...
you should always provide DDL and some sample data ...
So that we can understand your data and help.
Regards
Skybvi

Agreed

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ravinderm2003 SSC Journeyman Points: 92 More actions · Answer 9

Sorry about that, will do that next time. I guess I was not clear what I asked for. Thanks for correcting and I appreciate your help in correcting me.