T-sq doubt

Question

Post reply

T-sq doubt

dastagirid

SSCommitted

Points: 1810
More actions
December 9, 2008 at 4:21 am

#129206

hello,
what is the command for get last 10 records in datatable..
regards:
Giri.D
Thanks
Dastagiri.D

Viewing 14 posts - 1 through 13 (of 13 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

last 10 records

Based on what criteria?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

steveb. SSC-Forever Points: 46733 More actions · Answer 2

SELECT top 10 * FROM [YourTable] Order by YourId Desc

It really depends on how your table is orderd, otherwise the results will not really mean anything

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

steveb (12/9/2008)
SELECT top 10 * FROM [YourTable] Order by YourId Desc
It really depends on how your table is orderd, otherwise the results will not really mean anything

That's the first ten, Steve - OP has asked for the last ten 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Chris Morris (12/9/2008)
steveb (12/9/2008)
SELECT top 10 * FROM [YourTable] Order by YourId Desc
It really depends on how your table is orderd, otherwise the results will not really mean anything
That's the first ten, Steve - OP has asked for the last ten 😀

its sorted in descending order.

"Keep Trying"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Chirag (12/9/2008)
Chris Morris (12/9/2008)
steveb (12/9/2008)
SELECT top 10 * FROM [YourTable] Order by YourId Desc
It really depends on how your table is orderd, otherwise the results will not really mean anything
That's the first ten, Steve - OP has asked for the last ten 😀
its sorted in descending order.

What is? That's the point. Steve understands what I'm saying.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dastagirid SSCommitted Points: 1810 More actions · Answer 6

I WANT TO KNOW WHAT IS THE LAST 10 RECORDS IN MY DATABASE TABLES .IS ANY COMMAND IS THERE OR NOT ? ...

Thanks
Dastagiri.D

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

dastagirid (12/9/2008)
I WANT TO KNOW WHAT IS THE LAST 10 RECORDS IN MY DATABASE TABLES .IS ANY COMMAND IS THERE OR NOT ? ...

Please don't shout, UPPERCASE is shouting.

It's impossible to answer your question without more information. SQL Server has no fixed concept of "first" or "last", it's up to you to decide which column in your table should be used to determine an appropriate timeline.

If you have in autoincrementing ID column, then the 10 rows with the highest values in that column would be the most recent. You may not have an autoincrementing column. You tell us. There are no mindreaders here.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 8

Chris Morris (12/9/2008)
You tell us. There are no mindreaders here.

I knew you were going to say that.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Garadin (12/9/2008)
Chris Morris (12/9/2008)
You tell us. There are no mindreaders here.
I knew you were going to say that.

Thanks Seth made me larf that did! Now, about the Lottery tomorrow...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 10

dastagirid (12/9/2008)
I WANT TO KNOW WHAT IS THE LAST 10 RECORDS IN MY DATABASE TABLES .IS ANY COMMAND IS THERE OR NOT ? ...

You have to ask the last 10 based on what? Time? Are you recording the times that data was inserted? If not, it's not going to be possible. You have to have something to order the data by because SQL Server doesn't simply store the data in a stack such that you can get the last 10 records.

For example, if you have a clustered index on the table, say it's clustered on name, then the last two records one has a name of Achmed and the other has a name of Zack. One is going to one page and one is going to another, assuming there's a bunch of data in between the two. So they're not stored in the order they were entered, but in the order in which they belong within the index, but even that can be out of strict chronological order and even, out of the order of the index itself.

To arrive at a particular order of data, you must provide order criteria. That's just how it works.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dastagirid SSCommitted Points: 1810 More actions · Answer 11

hi,

sorry for what I wrote my lines in uppar case in my forums,Really I dont know it's indicates to shout....

Thanks

Giri.D

Thanks
Dastagiri.D

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 12

Chris Morris (12/9/2008)
Garadin (12/9/2008)
Chris Morris (12/9/2008)
You tell us. There are no mindreaders here.
I knew you were going to say that.
Thanks Seth made me larf that did! Now, about the Lottery tomorrow...

Realizing that i'm answering this "tomorrow", which makes it a little less funny, but

"You'll lose"

Repeat as needed

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

J-440512 SSCertifiable Points: 6285 More actions · Answer 13

There is an SQL command to do what you want, as has already been answered.

It is just that you have to understand the key concept, which I show below with several examples.

I have limited my example to the "last" 4 records but a higher number of records works the same. As shown below, the "last" records you will obtain depend on what is your criteria to order your record, i.e. by which column. Do you want the last 4 employees hired ? The 4 employee names sorted in alphabetical order ? The 4 lowest-paid employees ? (if I had put in a column for salary in myTable).

So compare the various results obtained and you will see that SQL Server can be give you the results you want, but you have to tell it WHAT it is you want.

[font="Courier New"]CREATE TABLE myTable

. (

. pk int NOT NULL PRIMARY KEY IDENTITY,

. Last_Name varchar(20),

. Date_Hired datetime,

. voodoo varchar(38)

. )

.INSERT INTO #myTable

. (

. Last_Name,

. Date_Hired,

. voodoo

. )

SELECT 'Joe', '2006-10-12', NEWID() UNION

SELECT 'Ken', '2006-11-12', NEWID() UNION

SELECT 'Luke', '2006-11-12', NEWID() UNION

SELECT 'Mike', '2006-10-11', NEWID() UNION

SELECT 'Ann', '2008-10-12', NEWID() UNION

SELECT 'Nina', '2007-10-12', NEWID() UNION

SELECT 'Eve', '2004-10-12', NEWID() UNION

SELECT 'Adam', '2006-10-12', NEWID() UNION

SELECT 'Tom', '2009-10-12', NEWID() UNION

SELECT 'Jerry', '2006-11-13', NEWID()

/*-----------------------------

select * from #myTable

-----------------------------*/

pkLast_NameDate_Hiredvoodoo

1Adam2006-10-12 00:00:00.0006E444A5A-457F-47DA-9830-5FAD7CE7869C

2Ann2008-10-12 00:00:00.000B6B8E539-ED3F-476B-9BA1-ADD3F32F244C

3Eve2004-10-12 00:00:00.000B865A6E8-595A-4FD0-ABFA-E176664F0264

4Jerry2006-11-13 00:00:00.00096684970-7ACE-4416-8492-DC803197E33D

5Joe2006-10-12 00:00:00.000C9EC56EE-3B21-4A65-8A4F-C910F5414F85

6Ken2006-11-12 00:00:00.000E6FCE262-61DA-4359-B96A-566D55C9DCB1

7Luke2006-11-12 00:00:00.00096E37E47-3570-43D4-BE81-4876B3A52249

8Mike2006-10-11 00:00:00.000B2572FDB-5F13-44CA-B82B-D063A229C136

9Nina2007-10-12 00:00:00.0003EC16020-F53C-472D-9953-EDDCC61877EF

10Tom2009-10-12 00:00:00.000365331FD-1A35-4A77-B75A-6DF7A94F62F2

(10 row(s) affected)

/*-----------------------------

SELECT TOP 4 * FROM #myTable ORDER BY Last_Name DESC

-----------------------------*/

pkLast_NameDate_Hiredvoodoo

10Tom2009-10-12 00:00:00.000365331FD-1A35-4A77-B75A-6DF7A94F62F2

9Nina2007-10-12 00:00:00.0003EC16020-F53C-472D-9953-EDDCC61877EF

8Mike2006-10-11 00:00:00.000B2572FDB-5F13-44CA-B82B-D063A229C136

7Luke2006-11-12 00:00:00.00096E37E47-3570-43D4-BE81-4876B3A52249

(4 row(s) affected)

/*-----------------------------

SELECT TOP 4 * FROM #myTable ORDER BY Date_Hired DESC

-----------------------------*/

pkLast_NameDate_Hiredvoodoo

10Tom2009-10-12 00:00:00.000365331FD-1A35-4A77-B75A-6DF7A94F62F2

2Ann2008-10-12 00:00:00.000B6B8E539-ED3F-476B-9BA1-ADD3F32F244C

9Nina2007-10-12 00:00:00.0003EC16020-F53C-472D-9953-EDDCC61877EF

4Jerry2006-11-13 00:00:00.00096684970-7ACE-4416-8492-DC803197E33D

(4 row(s) affected)

/*-----------------------------

SELECT TOP 4 * FROM #myTable ORDER BY voodoo

-----------------------------*/

pkLast_NameDate_Hiredvoodoo

10Tom2009-10-12 00:00:00.000365331FD-1A35-4A77-B75A-6DF7A94F62F2

9Nina2007-10-12 00:00:00.0003EC16020-F53C-472D-9953-EDDCC61877EF

1Adam2006-10-12 00:00:00.0006E444A5A-457F-47DA-9830-5FAD7CE7869C

4Jerry2006-11-13 00:00:00.00096684970-7ACE-4416-8492-DC803197E33D

(4 row(s) affected)

/*-----------------------------

SELECT TOP 4 pk, Last_Name, Date_Hired, NEWID() AS Surprise FROM #myTable ORDER BY Surprise

-----------------------------*/

pkLast_NameDate_HiredSurprise

4Jerry2006-11-13 00:00:00.000D54234A1-8526-468A-AEE5-148D0C8BA57F

1Adam2006-10-12 00:00:00.000C828CE3A-DCAD-457A-88DD-17B4ED3571D5

10Tom2009-10-12 00:00:00.000DF40EB6E-4513-4694-98A5-2545C4B10B63

2Ann2008-10-12 00:00:00.0009DB7D22D-A4A7-4875-9AD7-30A48AE97B7C

(4 row(s) affected)

/*-----------------------------

SELECT TOP 4 pk, Last_Name, Date_Hired, NEWID() AS Surprise FROM #myTable ORDER BY Surprise

-----------------------------*/

pkLast_NameDate_HiredSurprise

6Ken2006-11-12 00:00:00.0009F69CD70-2184-4400-82DC-0E11B1C21D46

9Nina2007-10-12 00:00:00.000ECE0495E-8A62-428E-98FB-14B9DA76F8C1

8Mike2006-10-11 00:00:00.000CC65A61F-7C26-47D6-A52B-26F194AAF8C6

1Adam2006-10-12 00:00:00.00034525AED-6F68-4AD2-9205-4452E9EC248F

/*-----------------------------

SELECT TOP 4 pk, Last_Name, Date_Hired, NEWID() AS Surprise FROM #myTable ORDER BY Surprise

-----------------------------*/

pkLast_NameDate_HiredSurprise

2Ann2008-10-12 00:00:00.0006DCBAEC3-49DD-46FB-B982-2934E209ED17

1Adam2006-10-12 00:00:00.00044579015-2641-42D8-B20A-50A85EF8A4B1

8Mike2006-10-11 00:00:00.0007EC53CBA-D45F-425E-8C7A-61B1C690A364

9Nina2007-10-12 00:00:00.0009127BE08-70CF-477C-BFFB-7367EF83B9CB

(4 row(s) affected)

[/font]