February 1, 2010 at 6:15 am
Hi,
can anybody please tell me how to get last n records from a table.Just like top 3 returns the first three entered records,how can i find last three entered records.Can we achieve this without using Row_Number function.With Row_Number the problem is it uses order by,which changes the sequence in which the values were entered
February 1, 2010 at 6:17 am
February 1, 2010 at 6:29 am
No it will not give the last 3 entered rows , if ur rows are not in particular order in table.
Example the table it test
Id name
1 swe
2 ser
3 der
7 sdd
8 dre
4 sder
5 ser
the last three entered values in Id column are 8,4,5
I hope u got my point
February 1, 2010 at 6:37 am
SQL does not guarantee rows are held in the same physical order as they were entered, so you would have to use an order by clause with an identity value.
---------------------------------------------------------------------
February 1, 2010 at 6:58 am
Its not about physical dear.I want to get the last three logically entered values
February 1, 2010 at 7:00 am
Without a date or timestamp in the table there is no way of knowing which is the last 3 records added to the table.
February 1, 2010 at 7:00 am
sunil88_pal88 (2/1/2010)
Its not about physical dear.I want to get the last three logically entered values
Then you have to have some form of marker(unique incrementing identity/datettime) on the row to indicate in which order they went in. this is not recorded as standard. Do not view a table as a list of data, view it as a heap. If you need things out in a specific order YOU HAVE TO create the data to support that order.
February 1, 2010 at 7:33 am
sunil88_pal88 (2/1/2010)
Its not about physical dear.I want to get the last three logically entered values
Computers can't think. They don't use logic. They just do what they're told. Are you telling the computer which rows were added in what order? If so, you can request them that way. If not, it can't do what you haven't told it.
You can't get out of a machine what hasn't been put into it. That's right in there with the basic laws of thermodynamics.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2010 at 7:42 am
GSquared (2/1/2010)
sunil88_pal88 (2/1/2010)
Its not about physical dear.I want to get the last three logically entered valuesComputers can't think. They don't use logic. They just do what they're told. Are you telling the computer which rows were added in what order? If so, you can request them that way. If not, it can't do what you haven't told it.
You can't get out of a machine what hasn't been put into it. That's right in there with the basic laws of thermodynamics.
Why does nobody tell me these things?!!! All those years of correspondance school for alchemy wasted....
---------------------------------------------------------
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."
February 1, 2010 at 8:56 am
So why has microsoft has made some opposite of TOP .They should also have made BOTTOM
February 1, 2010 at 9:01 am
Why ? The Bottom X rows are the first X rows ordered descending.
February 1, 2010 at 9:03 am
sunil88_pal88 (2/1/2010)
So why has microsoft has made some opposite of TOP .They should also have made BOTTOM
You need to ask Microsoft that question, not us.
FYI, your last question has nothing to do with the original question.
Just like you were told you cannot tell which records were added last, you cannot tell which records were added first, unless you have a column that maintains that information for you.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 1, 2010 at 9:12 am
TOP n gives us the first n entries of a column
February 1, 2010 at 9:16 am
sunil88_pal88 (2/1/2010)
TOP n gives us the first n entries of a column
only if you use ORDER BY that column
AND ... there is no way of ordering by the order that data was entered unless you have a column or columns that maintain that information for you.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 1, 2010 at 9:23 am
select top(10) number from master..spt_values where type ='P'
go
select top(10) number from master..spt_values where type ='P' order by number
go
/* BOTTOM X Rows */
select top(10) number from master..spt_values where type ='P' order by number desc
go
Consider these 3 Queries.
The first will give you ANY 10 rows, yes ANY. Just because in this case its 0 to 9 this case , it is not guaranteed.
The second will return you the Top 10 rows ORDERED by number , so that is guaranteed to be 0 to 9
The Third (BOTTOM) will return you the first 10 rows from the list Order by number descending.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply