2

I have a table like this

+------------+
| Id| data   |
|============|
| n | some_n |
| a | some_a |
| f | some_f |
| b | some_b |
+------------+

Is there a way to get next "N" rows if I know the prev ID row only? for example, I know the id for the second row (a) and I would like get next 2 rows from there. query will return {(f, some_f), (b, some_b)}

query looks like something this:

SELECT * FROM table WHERE [something] LIMIT start,numRows;

The question is... How to I know the start value? and what I put in where clause?

papelucho
  • 267
  • 1
  • 3
  • 10

1 Answers1

2
SELECT t.* FROM table t WHERE t.Id > 'a' ORDER BY t.Id LIMIT 2;

There is no implied order in rows in a table; so absent an ORDER BY clause, the database is free to return rows in an arbitrary order. And absent an ORDER BY, it's not possible to guarantee that a subsequent query will not return a previously returned rows.

So, for you to know that the "second row" has an Id value of 'a', that implies that some ordering has been applied. My example query assumes that the rows are ordered by the Id column.

With a specified ordering, it's possible for a query to return the "next N" rows, using a predicate (condition in the WHERE clause) that specifies that only rows "following" the last retrieved value are to be returned. The LIMIT clause is applied after the rows are ordered, so that we are guaranteed to get the "next N rows" (in ordered sequence.)

The more general form, if the rows are being ordered on something other than a unique key, requires a slightly more complicated predicate, to handle the equality condition, where the next row to be retrieved has a value that matches the last retrieved...

SELECT t.*
  FROM table t
 WHERE ( t.col = last_col_value_retrieved AND t.id > last_id_value_retrieved )
    OR ( t.col > last_col_value_retrieved )
 ORDER BY t.col, t.id
 LIMIT 50
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Well I think the best way to make this is to have an ORDER BY statement. – papelucho Jun 08 '13 at 18:06
  • @papelucho: yes, an `ORDER BY` clause is the only way to get this to work correctly. You need to return the expression(s) in the `ORDER BY` clause (in the he SELECT list) and use the last retrieved value in a subsquent query, to be able to retrieve the next N rows. – spencer7593 Jun 09 '13 at 00:20