If I'm querying a database, how can I get the 5th row back from a given row?

Question

If I have a row id and I want the 5th row back from that row how would I do this?

Normally I could just go

rowId - 5

and that would be the id of the 5th row back...

However, I need to account for if a row has been deleted, this would mean that, for example

39, 40, 41, 42, 43, 44, 45

if rowId was 45, then I would get rowId of 40 for my 5th row back, but if row 42 was deleted, then we would have...

39, 40, 41, 43, 44, 45

and the rowId of 45 would give me a rowId of 40 again, when it should be giving me 39 (because it is the 5th row back).

I can't think of any way to get around this, I can't renumber the id's after a deletion because the table may grow to be a million rows long. Anyone have any ideas?

Thanks, Matt

Answer 1

If you're on Sql Server 2005 or higher, you can use ROW_NUMBER:

SELECT *
FROM (
    SELECT TOP 5
         ROW_NUMBER() OVER (ORDER BY YourId DESC) AS RowNr,
         *
    FROM YourTable
    WHERE YourId < CurrentId
    ORDER BY YourId DESC
) vw
WHERE RowNr = 5

The "ORDER BY ID ASC" specifies how you want to rows numbered; you can change it to pretty much any sort order, for example:

ROW_NUMBER() OVER (ORDER BY YourTimestamp DESC) AS RowNr,

Would be helpful in a query looking for a previous row based on age.

Answer 2

You can use the ROW_NUMBER ranking functions of T-SQL...

Here's some pseudo-SQL code:

SELECT id, value,
   ROW_NUMBER() OVER ( ORDER BY id) AS RowNo
FROM ranking AS r1
WHERE RowNo = selectedRowNo - 5

Answer 3

Looks to me like you want the 5th row back based on sequential ids (which has nothing to do with physical order in the db)?

If this is what you want and you know the id you want to start with, say 45, you could

select top 5 * from table where Id < 45 order by id desc

Answer 4

I realize this is double-nested, but if you're not doing it very often, and/or the RowNum column is indexed, it shouldn't cause too much trouble:

SELECT *
  FROM YourTable
 WHERE RowNum = (SELECT MIN(RowNum)
                   FROM (SELECT TOP 5 RowNum
                           FROM YourTable
                       ORDER BY RowNum DESC) t)

Also, this solution will work with older versions of SQL as well, whereas the "over" clause is 2005+ only.

Answer 5

Building on automatic's answer

select top 1 * from (
     select top 5 * from table where Id < 45 order by id desc
) as fiverows order by id asc

This will give the 5th row back, and only the 5th row back.

Note: Select * is baaaaaad, mkay? :-)

Answer 6

I generally use a CTE for numbering. Something like this would do:

;WITH NumberedRows
AS
(SELECT   ID, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RowNumber
FROM      MyTable)
SELECT    ID
FROM      NumberedRows
WHERE     RowNumber = (SELECT RowNumber FROM NumberedRows WHERE ID = @MyID) - 5

It's not vastly different from any of the solutions already provided, but I find the CTE's abstraction makes the code's intent much clearer.