1

I have some query on a table which can be sorted by any of several columns, and an id of a row in the resultset. How can I get the next or previous records (or the offset of the id in the complete resultset).

ie. let's say I have the following table

ID First_Col Second_Col Name
1   34        24         John
2   40        22         James
3   48        5          Hugh

If sorting by first_col, and given id 2, the next record would be 3, but if sorting by Second_Col, the next record would be 1.

Given the sortBy expression (which is passed to my function), and the id, how can I find the next record easily?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Yuval
  • 504
  • 7
  • 17

2 Answers2

2

This should work:

SELECT
*
FROM
names
WHERE
Second_Col > (SELECT Second_Col FROM names WHERE ID = 2)
ORDER BY
Second_Col 
LIMIT 1

See http://sqlfiddle.com/#!2/fac71/3

Ross Smith II
  • 11,799
  • 1
  • 38
  • 43
0

Here you are: http://sqlfiddle.com/#!3/d8761/5

Probably it can be done more efficient but it works:

SELECT T.ID from TBL T WHERE 
T.SECOND_COL=
(SELECT MIN(TT.SECOND_COL) FROM TBL TT WHERE TT.SECOND_COL>(SELECT TTT.SECOND_COL FROM TBL TTT WHERE ID=2))
Samson
  • 2,801
  • 7
  • 37
  • 55
  • First, your example is using MS SQL Server, not MySQL, and you're right, it *can* be done more efficiently here: http://stackoverflow.com/a/12131322/1432614 – Ross Smith II Aug 26 '12 at 16:29
  • 1
    MS SQL? This is standard ANSI SQL. Where did you come up with this? – Samson Aug 26 '12 at 17:06