SQL Query to Select the 'Next' Record (Similar to First or Top N)

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

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
      )

Select the first 150 rows, then the next 150 and so on?

In Oracle you have the nice rownum: it is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

SELECT 
    a, b
FROM
    (SELECT rownum rn, a, b from table WHERE c=some_value ORDER BY some_column)
WHERE 
    rn BETWEEN 150 AND 300;

(thanks to @Mark Bannister)

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.

Get previous and next row from rows selected with (WHERE) conditions

you didn't specify your DBMS, so the following is ANSI SQL:

select prev_word, word, next_word
from (
    select id, 
           lag(word) over (order by id) as prev_word,
           word,
           lead(word) over (order by id) as next_word
    from words
) as t
where word = 'name';

SQLFiddle: http://sqlfiddle.com/#!12/7639e/1

Select last record after selecting top N

You need to use your subquery as a derived table and select from that instead:

select top 1 *
from (select top 5 * 
      from tbl_dtr 
      where work_date < '2019/09/10' 
      order by Work_date asc) t
order by Work_date desc

How to skip the first n rows in sql query

Query: in sql-server

DECLARE @N INT = 5 --Any random number

SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
              , ID --Add any fields needed here (or replace ID by *)
        FROM TABLE_NAME
) AS tbl 
WHERE @N < RoNum
ORDER BY tbl.ID

This will give rows of Table, where rownumber is starting from @N + 1.

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

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

How to get next/previous record in MySQL?

next:

select * from foo where id = (select min(id) from foo where id > 4)

previous:

select * from foo where id = (select max(id) from foo where id < 4)

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

• maintain status in 2 places
• preserve status history
• ...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

---

Related Topics