Search code examples
mysqlsqlselectrow-number

SQL, how to prevent row number reset after where condition

I have this query:

SELECT ROW_NUMBER() OVER (ORDER BY total_votes desc, views desc, date desc, time desc) AS rn,
name, date, time, subject, full_address, views, total_votes, user_email, typology_name
FROM photo
ORDER BY total_votes desc, views desc, date desc, time desc;

that produces this result:

result .

Now, I want to filter the result table so for example I only see lines 2/3/4:

SELECT ROW_NUMBER() OVER(ORDER BY total_votes desc, views desc, date desc, time desc) AS row_num,
name, date, time, subject, full_address, views, total_votes, user_email, typology_name
FROM(
SELECT *, ROW_NUMBER() OVER (ORDER BY total_votes desc, views desc, date desc, time desc) AS rn
FROM photo) q
WHERE rn >= 2 AND rn <= 4 
ORDER BY total_votes desc, views desc, date desc, time desc;

The problem is that this query returns those 3 lines but putting 1/2/3 as row_num instead of 2/3/4. result 2

What could I do to see the row numbers I saw in the first query? Thanks in advance.

Solution

  • You're performing the second calculation of the row number over outer query, not inner. So it returns row numbers of rows in filtered query. You should use this code:

    SELECT rn, name, date, time, subject, full_address, views, total_votes, user_email, typology_name
FROM(
SELECT *, 
ROW_NUMBER() OVER (ORDER BY total_votes desc, views desc, date desc, time desc) AS rn
FROM photo) q
WHERE rn >= 2 AND rn <= 4 
ORDER BY total_votes desc, views desc, date desc, time desc;