Select top and bottom rows

Question

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

SELECT TOP(5) BOTTOM(5)

Of course BOTTOM does not exist, so I need another 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.

Answer 1

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 2

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