Querying the last 100 entered records

Question

Querying the last 100 entered records

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
November 3, 2008 at 7:57 am

#193220

I'm trying to return the last 100 or so records that were entered on a table. I guess by primary key or identity key, but I'm not sure I'm using the right syntax b/c it errors out.
Does anyone know what's the effective way to get such a list?
thx,
John

Viewing 7 posts - 1 through 7 (of 7 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

latingntlman (11/3/2008)
I'm trying to return the last 100 or so records that were entered on a table. I guess by primary key or identity key, but I'm not sure I'm using the right syntax b/c it errors out.
Does anyone know what's the effective way to get such a list?
thx,
John

SELECT TOP 100 Col1, Col2

FROM MyTable

ORDER BY WhatEver DESC

By the way, when you post a question it is better to post more details. For example what was the query that you used? What error did you get? Table's DDL, etc'.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 2

Sorry I wasn't explicit enough. I may be off base here, but if I want to lookup the last X number of records based on their GUID or Identity, is that possible?

John

WayneS SSC Guru Points: 95461 More actions · Answer 3

To get the last 100 from an identity column, it would be as Adi said:

select top 100 *

from MyTable

order by MyIdentityColumn desc

this gets the last 100 values from the MyIdentityColumn - note that they will be sorted in descending order.

I don't know how you would get the last 100 entries based off of a GUID datatype.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

To get the latest 100 rows, you need some column that stores the date that the row was inserted, or you need an identity column (or a uniquidentifier with a default of newsequentialguid). There's no built-in way to tell when a row was inserted into a table. Without a column that in some way stores the order inserted (and a guid defaulted to newID doesn't), you cannot identify the last 100 rows inserted.

Can you give us the structure of the table in question?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 5

I understand. The way suggested works just fine. I was just curious to know if it was possible to do this looking up GUID.

Regards,

John

Jerry Hung SSChampion Points: 12968 More actions · Answer 6

latingntlman (11/6/2008)
I understand. The way suggested works just fine. I was just curious to know if it was possible to do this looking up GUID.
Regards,
John

GUID I would say not. NEWID()

Sequential GUID I would think so. NEWSEQUENTIALID()

SQLServerNewbieMCITP: Database Administrator SQL Server 2005