Search code examples
sqlrow-number

SQL code not working properly. Output keeps changing (Varying)

Select * from 
(SELECT * ,   
    (ROW_NUMBER() OVER(PARTITION BY age) ) as rn FROM `usr`    
ORDER BY  sname ASC ) temp 
where (`rn` = 2) and (location="Sz")

Hi, when I test this code in phpmyadmin, it returns different values each time run.

Solution

  • A table and also a result set is unsorted, only when you give the data an ORDER BY you get deterministic results.

    also your query should look like this, in case you want the second. oldest

    But it would be better like MatBailie mentioned in the comment to have another column as sorting order , because if many have 60, i still think that you will get always the same result, but to control the results better another column for sorting would help.

    Select * from 
(SELECT * ,   
    (ROW_NUMBER() OVER(PARTITION BY age ORDER BY age DESC) ) as rn FROM `usr`    
ORDER BY  sname ASC ) temp 
where (`rn` = 2) and (location='Sz')