Get all rows except last 5 rows

Question

I have a question very similar to this one:

Where I need to get all rows except the last 5 rows. How can I do that?

I tried by using row_number() over (partition by ... order by) but it's not working

Select all rows `minus` the last N rows.... Actually, I think `EXCEPT` might be the equivalent keyword in SQL Server. But anyway, follow that concept. — Marc, Nov 15 '20 at 18:32
If you order the data in the "wrong order" then just `OFFSET` the first 5 rows. The "last 5" are the "first 5" if in the opposite order. — Thom A, Nov 15 '20 at 18:44
[EXCEPT](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15) is explained here. — Luuk, Nov 15 '20 at 18:52
Be careful what you assume. What happens when there are no more than 5 rows total? — SMor, Nov 15 '20 at 19:39

score 3 · Answer 1 · answered Nov 15 '20 at 18:48

3

Instead of thinking you need to omit the "last 5" all you need to if omit the first 5 in the opposite order. Then the answer is simple with an OFFSET:

SELECT *
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I)
ORDER BY V.I DESC
OFFSET 5 ROWS;

If the return order is "important" then use a subquery:

SELECT sq.I
FROM (SELECT *
      FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I)
      ORDER BY V.I DESC
      OFFSET 5 ROWS) sq
ORDER BY sq.I;

answered Nov 15 '20 at 18:48

Thom A

88,727
11
45
75

nice, so this offset will give me the rows excluding 5 rows and how can i fetch that 5 rows remaining – Jan Nov 15 '20 at 18:56
Using `TOP` @Jan . A simple search would give that the answer to *that* question. – Thom A Nov 15 '20 at 18:58
If this answers the question, please do consider marking it as the solution so that future readers know it was useful, @Jan . – Thom A Nov 15 '20 at 19:49

score 0 · Answer 2 · answered Nov 15 '20 at 19:02

A solution using EXCEPT:

SELECT *
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I)
EXCEPT
SELECT *
FROM (SELECT TOP 3 * 
      FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))X(I)
      ORDER BY I DESC) X;

result:

I
-----------
1
2
3
4
5
6
7

Get all rows except last 5 rows

2 Answers2