return bottom 2 rows

Question

Post reply

return bottom 2 rows

Ross Anderson

Grasshopper

Points: 19
More actions
August 9, 2005 at 8:30 pm

#165214

Say I have a table with rows:
hello
goodbye
how
are
you
And I want to retrieve the bottom 2 with some querystring. If I were to SELECT TOP 2 * FROM table DESC it would reverse them, so I'd get:
you
are
[instead of]
are
you
I want the latter (are, you). Any way around this?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

create table #foo

(

row_id int identity(1,1) primary key,

word varchar(30)

)

go

insert #foo(word)

select 'hello'

union all

select 'goodbye'

union all

select 'how'

union all

select 'are'

union all

select 'you'

go

SELECT top 2 word from (select TOP 2 * FROM #foo order by row_id DESC)w order by row_id asc

HTH

Mathew J Kulangara
sqladventures.blogspot.com

Rani Shoura SSC Veteran Points: 219 More actions · Answer 2

Limiting Result Sets Using TOP and PERCENT

The TOP clause limits the number of rows returned in the result set.

TOP n [PERCENT]

n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return:

TOP 120 /*Return the top 120 rows of the result set. */TOP 15 PERCENT /* Return the top 15% of the result set. */.

If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.

MS SQL SERVER HELP

Hold on hold on soldier
When you add it all up
The tears and marrowbone
There's an ounce of gold
And an ounce of pride in each ledger
And the Germans killed the Jews
And the Jews killed the Arabs
And Arabs killed the hostages
And that is the news
And is it any wonder
That the monkey's confused