I have a table with two columns. First name,second name. There are 100 rows in that. No keys on this table.
I want last 30 rows . I can do this by cursors. How it can be solved through normal query.
asked
how to select Bottom( N) rows in a table through single query.
I second Phil's point. It is a mistake to assume a table possesses any "natural" order other than that which is established with an ORDER BY clause. For example, if you wanted the last 30 based on their order of insertion into the table, you must have a timestamp or identity column already in the table which preserves that order.
Use TOP, with the reverse order.
SELECT TOP (30) *
FROM Table
ORDER BY LastName DESC, FirstName DESC;
Overall I agree with Rob Farley, but in case you want to preserve original order, you might want to use the following version:
SELECT *
FROM
(
SELECT TOP (30) *
FROM YourTable
ORDER BY LastName DESC, FirstName Desc
)
ORDER BY LastName ASC, FirstName Asc
or if you don't like subqueries:
SELECT TOP (30)
*,
ROW_NUMBER() OVER (ORDER BY LastName DESC, FirstName DESC) as RowNum
FROM YourTable
ORDER BY RowNum DESC
The subquery shown above works correctly. However, in SQL 2012 and maybe other versions, you need to add an Alias name to the subquery to make the syntax correct. An " A" after the ) works. The Row-Number example didn't work for me, the row numbers were inversely ordered, but it returned the first block, not the last block. I think a subquery is required.
2 People are following this question.
