Search code examples
mysqlsqlrownum

ROWNUM MYSQL giving 1 as row numbers

I am running following query in mysql and it is giving me just "1" as row_num instead of auto incrementing it. Can anyone please guide me what is wrong

SELECT ROW_NUMBER() OVER(PARTITION BY DISEASEIDPK) AS row_num,COUNT(*) PATIENTSAVAILABLE, DISEASEIDPK, 
(Select DISEASENAME from doctorsoncallschema.diseases d where d.DISEASEIDPK = doctorsoncallschema.patients.DISEASEIDPK) as DISEASENAME
FROM doctorsoncallschema.patients  
GROUP BY DISEASEIDPK ORDER BY row_num ASC;

Result

enter image description here

Solution

  • It looks like you want to order using the DISEASEIDPK column, rather than partitioning by it. If so, then use:

    SELECT ROW_NUMBER() OVER (ORDER BY DISEASEIDPK) AS row_num

    Using PARTITION BY DISEASEIDPK means that every record having the same DISEASEIDPK value will be assigned the same row number.