Get the N iteration of an SQL query-CodePudding

Is there a way to get the N iteration of an SQL query ?

For example, if I want the second iteration :

Backup

id	id_device	nb_cut
11	222	853
10	5	698
9	222	589
8	5	123
7	222	456
...	...	...

SELECT nb_cut FROM Backup WHERE id_device = 5 ORDER BY id DESC;

This query return 698. But I want the seconde iteration whose result would be 123.

CodePudding user response：

Use LIMIT OFFSET

SELECT nb_cut 
FROM Backup 
WHERE id_device = 5  
ORDER BY id DESC LIMIT 1, 1;

https://dbfiddle.uk/qOOw2LJl

The query shown above gets data starting the second row and limits the results to 1.

CodePudding user response：

Literally the second result over a specific resultset filtered by id_device .... exists limit offset but this one could introduce you to a new world ;)

SELECT 
    nb_cut 
FROM (
  select 
     *, 
     row_number() OVER ( PARTITION BY id_device ORDER BY id DESC) as iteration 
  from Backup
) as t 
WHERE id_device = 5 and iteration = 2

Look for "window functions" on the web for explanation