x

how to select Bottom( N) rows in a table through single query.

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.

more ▼

asked Oct 25 '09 at 04:08 AM in Default

Raja M gravatar image

Raja M
75 2 2 2

You don't specify the order that you want the rows in.
Oct 25 '09 at 05:37 AM Phil Factor
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.
Oct 25 '09 at 09:44 AM Bob Hovious
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Use TOP, with the reverse order.

SELECT TOP (30) * FROM Table ORDER BY LastName DESC, FirstName DESC; 
more ▼

answered Oct 25 '09 at 04:13 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

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 
more ▼

answered Oct 25 '09 at 05:44 AM

AlexS gravatar image

AlexS
117 2

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jan 24 '13 at 03:34 PM

Jim_Mitten gravatar image

Jim_Mitten
20

+1 Yes a table alias is required
Jan 24 '13 at 09:40 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x341

asked: Oct 25 '09 at 04:08 AM

Seen: 31998 times

Last Updated: Jan 24 '13 at 09:40 PM