Select top and bottom rows

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 sol开发者_运维知识库ution that I'm not getting. Doing the select again with GROUP BY DESC is not an option.

---

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

---

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

---

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

---

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}

---

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) 
  )

---

No real difference between this and the union that I'm aware of, but technically it is a single query.

select t.* 
from table t
where t.id in (select top 5 t2.id from table t2 order by MyColumn)
    or
    t.id in (select top 5 t2.id from table t2 order by MyColumn desc);

---

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.

---

SELECT *
FROM (
SELECT x, rank() over (order by x asc) as rown
FROM table
) temp

where temp.rown = 1
or temp.rown = (select count(x) from table)

---

I had to do this recently for a very large stored procedure; if your query is quite large, and you want to minimize the amount of queries you could declare a @tempTable, insert into that @tempTable then query from that @tempTable,

DECLARE @tempTable TABLE ( columns.. )
INSERT INTO @tempTable
VALUES ( SELECT.. your query here ..)

SELECT TOP(5) columns FROM @tempTable ORDER BY column ASC -- returns first to last
SELECT TOP(5) columns FROM @tempTable ORDER BY column DESC -- returns last to first