3

Is there any way to select the next 10 rows after the row with ID where rows are ordered by something.

I've come up only with simple solution like:

  1. select all
  2. apply a loop to find ID and then return next 10

Thanks!

typedef
  • 1,800
  • 3
  • 11
  • 19

1 Answers1

3

You can use LIMIT . See the docs:

SELECT * from tableName
WHERE id > yourID
ORDER BY ID ASC
LIMIT NumRows

sqlfiddle demo

If the rows aren't orderd by ID, you can do:

SELECT * FROM tab1
WHERE orderColumn > (
  SELECT orderColumn from tab1
  WHERE id = YourId
)
ORDER BY orderColumn
LIMIT 10

sqlfiddle demo

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Yes, thanks! But the problem is I don't know the RowToStart. I just know its ID – typedef Nov 30 '13 at 21:01
  • +1: nice! I'm not too experienced with postgres, but my intuition says this problem could be even better solved by using cursors (given the plan for this query). Do you know about that? – Marcelo Zabani Nov 30 '13 at 21:25
  • I wouldnt use them, but there are some discussions around here about using them for something like this. see [here](http://stackoverflow.com/q/13142834/1385896) – Filipe Silva Nov 30 '13 at 21:30
  • Cool, thank you for this solution! There still is a little problem: what if orderColumn is not unique? – typedef Dec 01 '13 at 06:15
  • @typedef You can do >= that select and put id <> yourid in the outside where – Filipe Silva Dec 01 '13 at 12:17