How to find the next record after a specified one in SQL?

Question

Hi,

I'd like to use a single SQL query (in MySQL) to find the record which comes after one that I specify.

I.e., if the table has:

id, fruit
--  -----
1   apples
2   pears
3   oranges

I'd like to be able to do a query like:

SELECT * FROM table where previous_record has id=1 order by id;

(clearly that's not real SQL syntax, I'm just using pseudo-SQL to illustrate what I'm trying to achieve)

which would return:

2, pears

My current solution is just to fetch all the records, and look through them in PHP, but that's slower than I'd like. Is there a quicker way to do it?

I'd be happy with something that returned two rows -- i.e. the one with the specified value and the following row.

EDIT: Sorry; my question was badly specified. Unfortunately, my definition of "next" is not based on id, but on alphabetical order of fruit name. Hence, my example above is wrong, and should return oranges, as it comes alphabetically next after apples. Is there a way to do the comparison on strings instead of ids?

Thanks,

Ben

Answer 1

After the question's edit and the simplification below, we can change it to

SELECT id FROM table WHERE fruit > 'apples' ORDER BY fruit LIMIT 1

Answer 2

I'm not familiar with the MySQL syntax, but with SQL Server you can do something with "top", for example:

SELECT TOP 1 * FROM table where id > 1 order by id;

This assumes that the id field is unique. If it is not unique (say, a foreign key), you can do something similar and then join back against the same table.

Since I don't use MySQL, I am not sure of the syntax, but would imagine it to be similar.

Answer 3

How about this:

Select * from table where id = 1 + 1

Answer 4

Unless you specify a sort order, I don't believe the concepts of "previous" or "next" are available to you in SQL. You aren't guaranteed a particular order by the RDBMS by default. If you can sort by some column into ascending or descending order that's another matter.

Answer 5

SELECT * FROM table WHERE id > 1 ORDER BY id LIMIT 1

Even simpler

UPDATE:

SELECT * FROM table  WHERE fruit > 'apples' ORDER BY fruit LIMIT 1

Answer 6

I don't know MySQL SQL but I still try

select n.id 
from   fruit n
,      fruit p
where  n.id = p.id + 1;

edit:

select n.id, n.fruitname 
from   fruits n
,      fruits p
where  n.id = p.id + 1;

edit two:

Jason Lepack has said that that doesn't work when there are gaps and that is true and I should read the question better.

I should have used analytics to sort the results on fruitname

select id
,      fruitname
,      lead(id) over (order by fruitname)  id_next
,      lead(fruitname) over (order by fruitname) fruitname_next
from   fruits;

Answer 7

This should work. The string 'apples' will need to be a parameter.

Fill in that parameter with a string, and this query will return the entire record for the first fruit after that item, in alphabetical order.

Unlike the LIMIT 1 approach, this should be platform-independent.

--STEP THREE: Get the full record w/the ID we found in step 2
select *
from 
  fruits fr
 ,(   
  --STEP TWO: Get the ID # of the name we found in step 1
  select
    min(vendor_id) min_id
  from 
    fruits fr1
   ,(
    --STEP ONE: Get the next name after "apples"    
    select min(name) next_name
    from fruits frx
    where frx.name > 'apples'
    ) minval
  where fr1.name = minval.next_name
  ) x
where fr.vendor_id = x.min_id;

The equivalent to the LIMIT 1 approach in Oracle (just for reference) would be this:

select *
from
  (
  select *
  from fruits frx
  where frx.name > 'apples'
  order by name
  )
where rownum = 1

Answer 8

So simple, and no gymnastics required

Select * from Table
where id = 
    (Select Max(id) from Table
     where id < @Id)

or, based on the string @fruitName = 'apples', or 'oranges' etc...

Select * from Table
where id = 
    (Select Max(id) from Table
     where id < (Select id from Table
                 Where fruit = @fruitName))