Question 22090 in Mysql Question posted in
The official MySQL documentation can be found here.

Get the N iteration of an SQL query – Mysql

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search