CODEWITHSUNDEEP
sqlsql-serverselect
Matt
Matt

Reputation:

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

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

Upvotes: 0

Views: 256

Answers (6)

Aaron Alton
Aaron Alton

Reputation: 23226

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.

Upvotes: 1

Dan F
Dan F

Reputation: 12052

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

Upvotes: 2

SqlRyan
SqlRyan

Reputation: 33914

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.

Upvotes: 1

Andomar
Andomar

Reputation: 238116

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.

Upvotes: 2

Arjan Einbu
Arjan Einbu

Reputation: 13672

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

Upvotes: 3

automatic
automatic

Reputation: 2737

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

Upvotes: 1

Related Questions