CODEWITHSUNDEEP
sqlsql-server-2008-r2
Haminteu
Haminteu

Reputation: 1334

row number partition doesn't work

I've a problem. Don't know why.

select ROW_NUMBER() OVER (PARTITION BY a.rmid, c.nama ORDER BY a.tanggalrekam) rn,
       a.rmid, a.psid, c.nama, a.tanggalrekam
from _tRekamMedis a
inner join _tRekamMedisTindakan b on a.rmid= b.rmid
inner join _tPasien c on a.psid = c.psid
inner join _tSecurityUser d on b.dokterid = d.securityuserid
inner join _tTindakan e on b.tindakanid = e.tindakanid

result:

    rn  rmid    psid    nama        tanggalrekam    dokterid
    1   1       1       Fauzan      2017-06-20      -2147483648
    2   1       1       Fauzan      2017-06-20      -2147483648
    3   1       1       Fauzan      2017-06-20      -2147483648
    1   2       2       Huri        2017-06-20      -2147483648

It should be:

    rn  rmid    psid    nama        tanggalrekam    dokterid
    1   1       1       Fauzan      2017-06-20      -2147483648
    1   1       1       Fauzan      2017-06-20      -2147483648
    1   1       1       Fauzan      2017-06-20      -2147483648
    2   2       2       Huri        2017-06-20      -2147483648

the rownumber doesn't work. It shows on rn field. Anyone can help?

Upvotes: 0

Views: 5064

Answers (2)

Ven
Ven

Reputation: 2014

Include all columns for partition

select ROW_NUMBER() OVER (PARTITION BY a.rmid, a.psid,c.nama,a.tanggalrekam ORDER BY a.tanggalrekam) rn,
       a.rmid, a.psid, c.nama, a.tanggalrekam
from _tRekamMedis a
inner join _tRekamMedisTindakan b on a.rmid= b.rmid
inner join _tPasien c on a.psid = c.psid
inner join _tSecurityUser d on b.dokterid = d.securityuserid
inner join _tTindakan e on b.tindakanid = e.tindakanid

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You obviously don't want numbers per a.rmid, c.nama, so remove the PARTITION BY clause altogether. You are simply looking for DENSE_RANK without any partition:

SELECT DENSE_RANK() OVER (ORDER BY a.rmid, c.nama a.tanggalrekam) AS rn,
...

Upvotes: 3

Related Questions