Have Geek Brain

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

If I have a row id and I want the 5th row to return from that row, how would I do that?

Usually I could just go

rowId - 5

and that will be the id of the fifth line back ...

However, I need to take into account if the line was deleted it would mean, for example,

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

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

39, 40, 41, 43, 44, 45

and a rowId of 45 will give me a rowId of 40 again when it should give me 39 (because it's the fifth row back).

I cannot think of any way to get around this, I cannot renumber the id after deletion because the table can grow to a million rows. Does anyone have any ideas?

Thanks, Matt

0


a source to share


6 answers


You can use rank functions ROW_NUMBER for T-SQL ...

Here is some pseudo-SQL:



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


a source


If you are 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

"ORDER BY ID ASC" indicates how you want to number the lines; you can change it in almost any sort order, for example:



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

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

+2


a source


Create an automatic reply

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, but only the 5th row back.

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

+2


a source


It sounds like you want the 5th row to return based on sequential IDs (which has nothing to do with the physical ordering 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

+1


a source


I understand this is a double nesting, but if you don't do it very often and / or the RowNum column is indexed, this shouldn't cause too many problems:

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, while the "over" clause is only 2005+.

+1


a source


I usually use CTE for numbering. Something like that:

;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 doesn't differ much from any of the solutions already provided, but I find that the CTE abstraction makes the code clearer.

+1


a source






More articles:


All Articles