Last 5 rows

Question

Post reply

Last 5 rows

Viewing 15 posts - 31 through 45 (of 57 total)

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

dave hants Default port Points: 1485 More actions · Answer 1

dave hants

Default port

Points: 1485

November 16, 2011 at 1:36 pm

#1408850

Doh!

JediSQL SSCrazy Points: 2118 More actions · Answer 2

Charmer,

They are not in random order; they just have no defined order. The ANSI SQL standard says that if an ORDER BY is not specified, the server is to return the rows in the order that the server finds most efficient, and SQL Server does this. Thus, if you re-run the same query with the same tables with the same data, then you are likely to get the same results. In particular, if you SELECT * from a single table with a clustered index, you will likely get the rows in the cluster order. If from a heap (table with no clustered index), then rows will likely come back in the order they were inserted.

- Dan

Sincerely,
Daniel

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

per MSDN:

The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE, or DELETE are not arranged in any order. TOP n returns n random rows.

Charmer SSChampion Points: 14903 More actions · Answer 4

i want the last 5 rows based on last insertion...because i don't have any date time column...so how could i get it..?

more over my requirement is to get the recently updated records... for this week and for the last week...

how can i solve it...?

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 5

p-nut (11/16/2011)
david.moule (11/16/2011)
how do i select last 5 rows without any sorting order?
Do you have a business requirement behind this request? If so, what is it!
When you say the "last 5 rows" what do you mean. Do you mean the 5 rows that were most recently inserted or updated in your table? Or the first 5 rows that were inserted... Or something else?
What is your table structure and contents? And what is it that your user/customer really needs?
Rgds,
Dave.
Is there a parrot in the room? 🙂
Jared

yes there is a parrot...and its me:-)....i just started learning about SQL..Thank you so much guys for what you have discussed with each other on this topic...It was very useful to me...

Thanks,
Charmer

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

Charmer (11/17/2011)
i want the last 5 rows based on last insertion...because i don't have any date time column...so how could i get it..?
more over my requirement is to get the recently updated records... for this week and for the last week...
how can i solve it...?

Find the answer in your problem statement 😉

Charmer SSChampion Points: 14903 More actions · Answer 7

Charmer

SSChampion

Points: 14903

November 17, 2011 at 1:48 am

#1409020

so there is no way right?

Thanks,
Charmer

dave hants Default port Points: 1485 More actions · Answer 8

Although they may not always be in random order, I find it best if all rows are treated as being in a random order!

Until sorted by "order by".

🙂

dave hants Default port Points: 1485 More actions · Answer 9

i want the last 5 rows based on last insertion...because i don't have any date time column...so how could i get it..?
more over my requirement is to get the recently updated records... for this week and for the last week...

Hmmmm, tricky... What does the table look like and what is the content of the rows?

Charmer SSChampion Points: 14903 More actions · Answer 10

i have two tables....1st one has columns name RlseId and a datetime column..

2nd table has TrackID(primary key,automatic generated) , RlseId(foreign key of 1st table) and RlsTrackId...but no date time column...

whenever the RlsId is inserted into the 2nd table...TrackId is generated automatically...i want to get the TrackId for recent updates but the problem is RlsId got duplicates...

able to get RlsID on particular date & time from 1st table..but for the same Id i am not able to get TrackID from the 2nd table as RlsId got duplicates and no datetime column...

Thanks,
Charmer

Ant-Green SSC Guru Points: 113507 More actions · Answer 11

please post the DDL and sample data of the objects in the problem

Lowell SSC Guru Points: 323518 More actions · Answer 12

Charmer (11/17/2011)
i have two tables....1st one has columns name RlseId and a datetime column..
2nd table has TrackID(primary key,automatic generated) , RlseId(foreign key of 1st table) and RlsTrackId...but no date time column...
whenever the RlsId is inserted into the 2nd table...TrackId is generated automatically...i want to get the TrackId for recent updates but the problem is RlsId got duplicates...
able to get RlsID on particular date & time from 1st table..but for the same Id i am not able to get TrackID from the 2nd table as RlsId got duplicates and no datetime column...

well, doesn't that keep your data in the order inserted, so you can ORDER BY TrackID DESC?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 13

Dev (11/17/2011)
Charmer (11/17/2011)
i want the last 5 rows based on last insertion...because i don't have any date time column...so how could i get it..?
more over my requirement is to get the recently updated records... for this week and for the last week...
how can i solve it...?
Find the answer in your problem statement 😉

Curious... Is this always true? In many cases it will be acceptable, but you cannot guarantee this if in the script the getdate() is generated before the insertion is done. Not 100% sure you can "guarantee" it either way, although it is used all of the time as such. From my limited knowledge, the only way to guarantee insertion order is identity with identity_insert. And I'm not 100% sure on that.

If you are looking at recently updated records, that is an entirely new issue since obviously rows can be updated after insertion. I would add a createdDate and ModifiedDate column.

Jared

Jared
CE - Microsoft

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 14

p-nut (11/17/2011)
Dev (11/17/2011)
Charmer (11/17/2011)
i want the last 5 rows based on last insertion...because i don't have any date time column...so how could i get it..?
more over my requirement is to get the recently updated records... for this week and for the last week...
how can i solve it...?
Find the answer in your problem statement 😉
Curious... Is this always true? In many cases it will be acceptable, but you cannot guarantee this if in the script the getdate() is generated before the insertion is done. Not 100% sure you can "guarantee" it either way, although it is used all of the time as such. From my limited knowledge, the only way to guarantee insertion order is identity with identity_insert. And I'm not 100% sure on that.
If you are looking at recently updated records, that is an entirely new issue since obviously rows can be updated after insertion. I would add a createdDate and ModifiedDate column.
Jared

It was a general statement Jared. Just wanted OP to understand, he requires something to track order of insertion / modification.

Jared SSC Guru Points: 61793 More actions · Answer 15

Dev (11/17/2011)
p-nut (11/17/2011)
Dev (11/17/2011)
Charmer (11/17/2011)
i want the last 5 rows based on last insertion...because i don't have any date time column...so how could i get it..?
more over my requirement is to get the recently updated records... for this week and for the last week...
how can i solve it...?
Find the answer in your problem statement 😉
Curious... Is this always true? In many cases it will be acceptable, but you cannot guarantee this if in the script the getdate() is generated before the insertion is done. Not 100% sure you can "guarantee" it either way, although it is used all of the time as such. From my limited knowledge, the only way to guarantee insertion order is identity with identity_insert. And I'm not 100% sure on that.
If you are looking at recently updated records, that is an entirely new issue since obviously rows can be updated after insertion. I would add a createdDate and ModifiedDate column.
Jared
Got it 🙂 I was hoping to "spark" some conversation, but maybe it would be best for me to start a new topic. Look for it!
Jared
It was a general statement Jared. Just wanted OP to understand, he requires something to track order of insertion / modification.

Jared
CE - Microsoft