sql server - select top and bottom rows

Question

Hi,

I'm using Sql Server 2005 and i'm trying to achieve something like: In the same select statement i want to get the first x rows and the last x rows.

SELECT TOP(5) BOTTOM(5)

Of course 'bottom' does not exist so i need other solution. I believe there is an easy and elegant solution that i'm not getting. Doing the select again with Group By Desc is not an option.

Thanks.

Answer 1

Check the link

SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

Did you try to using rownumber?

SELECT * 
FROM 
(SELECT *, ROW_NUMBER() OVER (Order BY columnName) as TopFive
   ,ROW_NUMBER() OVER (Order BY columnName Desc) as BottomFive
   FROM Table
)
WHERE TopFive <=5 or BottomFive <=5

http://www.sqlservercurry.com/2009/02/select-top-n-and-bottom-n-rows-using.html

Answer 2

Then you are out - doing the select again IS the only option, unless you want to pull in the complete result set and then throwing away everything in between.

ANY sql I cna think of is the same way - for the bottom you need to know first either how many items you have (materialize everything or use count(*)) or a reverse sort order.

Sorry if that does not suit you, but at the end.... reality does not care, and I do not see any other way to do that.

Answer 3

Using a union is the only thing I can think of to accomplish this

select * from (select top(5) * from logins order by USERNAME ASC) a
union
select * from (select top(5) * from logins order by USERNAME DESC) b

Answer 4

i guess you have to do it using subquery only

select * from table where id in ( 
           (SELECT id ORDER BY columnName LIMIT 5) OR 
           (SELECT id ORDER BY columnName DESC LIMIT 5) 
  )


select * from table where id in ( 
           (SELECT TOP(5) id ORDER BY columnName) OR 
           (SELECT TOP(5) id ORDER BY columnName DESC) 
  )

EDITED

select * from table where id in ( 
           (SELECT TOP 5 id ORDER BY columnName) OR 
           (SELECT TOP 5 id ORDER BY columnName DESC) 
  )

Answer 5

Is it an option for you to use a union?

E.g.

select top 5 ... order by {specify columns asc} 
union 
select top 5 ... order by {specify columns desc}

Answer 6

I think you've two main options:

SELECT TOP 5 ...
FROM ...
ORDER BY ... ASC

UNION

SELECT TOP 5 ...
FROM ...
ORDER BY ... DESC

Or, if you know how many items there are in the table:

SELECT ...
FROM (
    SELECT ..., ROW_NUMBER() OVER (ORDER BY ... ASC) AS intRow
    FROM ...
) AS T
WHERE intRow BETWEEN 1 AND 5 OR intRow BETWEEN @Number - 5 AND @Number