Last 5 rows

Question

Post reply

Last 5 rows

Charmer

SSChampion

Points: 14903
More actions
November 16, 2011 at 6:13 am

#251912

Hi Friends,
how do i select last 5 rows without any sorting order?
Thanks,
Charmer

Viewing 15 posts - 1 through 15 (of 57 total)

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

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

How will you validate they are last 5? (if there is no ORDER BY clause)

Lowell SSC Guru Points: 323518 More actions · Answer 2

Charmer (11/16/2011)
Hi Friends,
how do i select last 5 rows without any sorting order?

you cannot.

without an order by, there is no first and last.

SELECT TOP 5 * FROM SOMETABLE will give you 5 rows, like you ask, but no Order By = logical mistake.

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!

Charmer SSChampion Points: 14903 More actions · Answer 3

ok, i understood...could you temme how top 5 works? in which order it works..?

Thanks,
Charmer

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

Random Order...

INSERT TOP (2) INTO Table2 (ColumnB)

SELECT ColumnA FROM Table1

ORDER BY ColumnA;

The ORDER BY clause in the previous query references only the rows that are returned by the nested SELECT statement. The INSERT statement chooses any two rows returned by the SELECT statement. To make sure that the top two rows from the SELECT subquery are inserted, rewrite the query as follows.

INSERT INTO Table2 (ColumnB)

SELECT TOP (2) ColumnA FROM Table1

ORDER BY ColumnA;

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

All rows must always be treated as being in a random order.

Until sorted by "order by".

Rgds,

Dave.

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 6

Try this..

select * from book where id not in (select top((select count(*) from book) - 5) id from book )

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Charmer SSChampion Points: 14903 More actions · Answer 7

this query does not work....it gives no records....

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 8

Random Order...!!!?

but how come it gives same records every time when we execute the query..?

it must change the order right?

Thanks,
Charmer

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

Charmer (11/16/2011)
Random Order...!!!?
but how come it gives same records every time when we execute the query..?
it must change the order right?

Fine.. Delete few rows & insert same rows again in your table. Query them again. You will see the difference.

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 10

Did you just copy and paste the query and ran?

You need to change based on tables available in your database. I ran it and worked fine for me as there was a table name book in my database.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Charmer SSChampion Points: 14903 More actions · Answer 11

yes....i understood now....Thanks Dev...

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 12

Sumanta Roy (11/16/2011)
Did you just copy and paste the query and ran?
You need to change based on tables available in your database. I ran it and worked fine for me as there was a table name book in my database.

yes i changed it according to my table set....but it ain't given the records....

Thanks,
Charmer

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

yes....i understood now....Thanks Dev...

Most Welcome 🙂

Charmer SSChampion Points: 14903 More actions · Answer 14

Hi Dev...did you check out with the query recommended by sumantaroy..?

i see no records as output...

Thanks,
Charmer