SQL Query to select the "Next" record (same as First or Top N)

Question

I need to make a request to return the next (or previous) record if any record is missing. For example, consider the following table:

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

I would like to request a record with identifier 7 or more if 7 is not.

My questions:

• Are these types of queries possible in SQL?
• What are queries called in the database world?

Thanks!

Answer 1

Yes, it is possible, but the 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 the SQL-2008 standard):

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

In those RDBMSs that have "window" functions (in the 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 don’t know which DBMS you have:

 select ID, value from YourTable where id = ( select min(id) from YourTable where id >= 7 ) 

Answer 2

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 

Answer 3

I would just do it like this:

 select top 1 * from myTable where id >=7 order by id 

the implementation of the top 1 part is T-SQL (MSSQL / Sybase), other implementations change, but it is always possible (mysql / postgre LIMIT 1 , oracle rownum = 1 )

Answer 4

 select top 1 * from Persons where Id >= @Id order by Id