CODEWITHSUNDEEP
sqlpostgresql
Bonnotbh
Bonnotbh

Reputation: 525

Selecting next n rows from a table, based on a given row and column value

I am currently trying to get the next or previous n lines from a table, the next 50 for argument's sake.

I will know a row, which could be anywhere within the data, and I will want to either get the next 50 or previous 50 based on the timestamp column. The problem is that the timestamp column isn't necessarily in order (although it probably will be), so therefore the next row in the data could have the wrong timestamp.

I suppose the best way to do it would be to create a temporary table, discard all the timestamps either before or after the given row (depending on whether searching for previous or next 50), sort by timestamp, and then select the next/previous 50 lines? I'm not really familiar with SQL past the basics.

Thanks in advance.

Upvotes: 3

Views: 1718

Answers (1)

Quassnoi
Quassnoi

Reputation: 425331

SELECT  *
FROM    mytable m
WHERE   (ts, id) <
        (
        SELECT  ts, id
        FROM    mytable mi
        WHERE   mi.id = :myid
        )
ORDER BY
        ts DESC, id DESC
LIMIT 50

Upvotes: 4

Related Questions