Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Query to Select the 'Next' record (similar to First or Top N)

I need to do a query to return the next (or prev) record if a certain record is not present. For instance consider the following table:

ID (primary key)    value
1                    John
3                    Bob
9                    Mike
10                   Tom.

I'd like to query a record that has id 7 or greater if 7 is not present.

My questions are,

  1. Are these type of queries possible with SQL?
  2. What are such queries called in the DB world?

Thanks!

like image 620
user983043 Avatar asked Oct 06 '11 21:10

user983043


2 Answers

Yes, it's possible, but implementation will depend on your RDBMS.

Here's what it looks like in MySQL, PostgreSQL and SQLite:

select ID, value
from YourTable
where id >= 7
order by id
limit 1

In MS SQL-Server, Sybase and MS-Access:

select top 1 ID, value
from YourTable
where id >= 7
order by id

In Oracle:

select * from (
    select ID, value
    from YourTable
    where id >= 7 
    order by id
)
where rownum = 1

In Firebird and Informix:

select first 1 ID, value
from YourTable
where id >= 7
order by id

In DB/2 (this syntax is in SQL-2008 standard):

select id, value
from YourTable
where id >= 7
order by id
fetch first 1 rows only

In those RDBMS that have "window" functions (in SQL-2003 standard):

select ID, Value
from (
  select 
    ROW_NUMBER() OVER (ORDER BY id) as rownumber,
    Id, Value
  from YourTable
  where id >= 7
) as tmp                  --- remove the "as" for Oracle
where rownumber = 1

And if you are not sure which RDBMS you have:

select ID, value
from YourTable
where id = 
      ( select min(id)
        from YourTable
        where id >= 7
      )
like image 130
Adriano Carneiro Avatar answered Oct 24 '22 07:10

Adriano Carneiro


Try this for MS-SQL:

SELECT TOP 1 
id, value 
FROM your_table
WHERE id >= 7
ORDER BY id

or for MySql

SELECT id, value 
FROM your_table
WHERE id >= 7
ORDER BY id
LIMIT 0,1
like image 1
Marco Avatar answered Oct 24 '22 07:10

Marco