Last Inserted Record using Efficient query

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

Last Inserted Record using Efficient query

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-25 : 16:47:58

Hi,
I've a table with following columns:

Table A
id, login, login_at
1 abc 22-May-2010
2 pqr 20-May-2010
3 abc 23-May-2010
4 abc 24-May-2010
5 pqr 22-May-2010

Now, I need an efficient query to find the last inserted record for a given login. For eg. if login is abc, then the query must give record with id 4

Can any one provide me the eficient query which would run very fast assuming there are millions records in this table?

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-25 : 17:02:19

[code]SELECT login,MAX(login_at)
FROM TableA
GROUP BY login[/code]

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-25 : 17:18:35

Hi,
Thanks for the reply. However, the query must return only 1 row. I need the last inserted record...

Thanks!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-25 : 17:19:44

Just the last inserted login?
Try this

SELECT TOP 1 * FROM TableA
ORDER BY login_at DESC

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-25 : 17:20:00

Sorry, Thanks for your soln...This is what I was expecting...

SELECT MAX(id) FROM TableAwhere login = 'abc 'GROUP BY login

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-25 : 17:20:45

Which one would be more efficient when there are million records in table A?

SELECT MAX(id) FROM TableA where login = 'abc' GROUP BY login

OR

SELECT TOP 1 * FROM TableA
ORDER BY login_at DESC

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-25 : 18:25:46

Hello,
can any one tell me which sql is better performance wise...?

Thanks!

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-05-26 : 05:39:59

SELECT MAX(login_at)
FROM TableA
WHERE login ='abc'

Lets unLearn

Kristen
Test

22859 Posts

Posted - 2010-05-26 : 06:45:29

"can any one tell me which sql is better performance wise...?"

You think we've got nothing better to do that provide free advice to you such that you need to chase us after an hour? People read the posts when they read them ... they don't make a note to come back again tomorrow! and they don;t need you chasing them. If your problem is business-critical then hire a consultant.

You have got three choices (at least):

SELECT MAX(id)
FROM TableA
where login = 'abc'
~~GROUP BY login~~ (GROUP BY is not required)

this is fine if you only want the ID, and no other columns

SELECT TOP 1 Col1, Col2
FROM TableA
ORDER BY login_at DESC

this is better if you want several columns (possibly NOT including the Sort Order column(s) itself), and this also lends itself to being restricted to a specific user:

SELECT TOP 1 Col1, Col2
FROM TableA
where login = 'abc'
ORDER BY login_at DESC

If you want the most recent login for multiple users, together with other columns from the row then you need to use ROW NUMBER OVER - ask for help if that is actually what you need.

The only way to know which is the most efficient is to check the query plan and Logical I/O - it will entirely depend on what indexes you have, and which actual Col1, Col2 you use with it.

If the performance is not fast enough you can use indexes to improve it - a covering index of "login_at, login" - possibly with INCLUDE columns for "Col1, Col2, ..." would help. If the selectivity of [login], within that index, is high enough then chaging the key order round to be "login, login_at" will be faster - but you run the risk that SQL does not use the index at all if [login] becomes insufficiently selective.

Finally, you could store the ID of the most recent transaction (in TableA) in a column in the "User" table, this would be the fastest of all. (If you don't want high-frequency of updates on the "User" table itself then create a table with just LoginID & LastLoginID columns). This can be kept up-to-date by, for example, a trigger on TableA. My guess is that this will perform the best if the number of Reads to Writes is of the ratio of 100:1 or higher. But again, you need to check Query Plans and Logical I/Os to know what works best in your particular database schema / real-world data etc. There is no "This is definitely faster than that" - (well, I can sometime say with certainly that a particular query will preform very badly ); any of these solutions is equally acceptable and probably similarly-performant IMHO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources