Select top 5 rows from a table except last row

Question

I need to select the last 5 rows from a table except the last record in MS SQL Server.

In other words:

SELECT TOP 5 * 
FROM table 
ORDER BY column DESC

but not considering the table last record in this select.

How can I do that?

possible duplicate of [Equivalent of LIMIT and OFFSET for SQL Server?](http://stackoverflow.com/questions/2135418/equivalent-of-limit-and-offset-for-sql-server) As well as the accepted answer, see also Somnath Muluk's answer if using SQLServer 2012. — , Oct 12 '13 at 19:01
I've posted @Mark suggestion as an answer since it will be clear for future readers. — Zanon, Sep 16 '14 at 00:35

score 0 · Answer 1 · answered Sep 16 '14 at 00:24

SQL Fiddle Example

It can be easily achieved in MS SQL Server 2012 using the following code:

BEGIN

  DECLARE
    @OFFSET int = 1,
    @LIMIT int = 5

  -- SQL Server 2012+
  SELECT id, name 
  FROM t 
  ORDER BY name DESC -- DESC because you want the list upside down
  OFFSET @OFFSET ROWS -- skipped rows
  FETCH NEXT @LIMIT ROWS ONLY -- number of rows

END;

For an older SQL Server version, use:

BEGIN

  DECLARE
    @OFFSET int = 1,
    @LIMIT int = 5

  -- SQL Server 2008
  ;WITH cte AS
  (
    SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name DESC) AS RowNum -- DESC because you want the list upside down
  FROM t
  )
  SELECT id, name
  FROM cte
  WHERE RowNum > @OFFSET
    AND RowNum <= @OFFSET + @Limit

END

Select top 5 rows from a table except last row

1 Answers1