Oracle Tips : Reset ROWNUM pada setiap group

Tips ini diperolehi dari laman web oracle dan diberikan oleh Satyanarayana Josyula, Sr. Oracle DBA at Nexus Energy Software, in Reston, Virginia. Untuk maklumat lanjut sila layari
http://www.oracle.com/technology/oramag/code/tips2004/082304.html

Kita menggunakan ROWNUM untuk menomborkan secara berturutan (seperti 1, 2, 3....) senarai rekod dari SELECT statement. Kadang-kadang terdapat keperluan untuk memaparkan laporan atau senarai rekod dengan nombor siri yang berturutan pada setiap rekod(rows) dan nombor tersebut akan bermula pada nombor 1 semula bagi kumpulan(group) yang baru (seperti contoh di bawah; lajur 4)

Contoh

Nama table : BATCHLIST
BATCH_NO STATE Jika guna ROWNUM Hasil yang dikehendaki
-------- --------- ---------------- ----------------------
A1 PERAK 1 1
A1 PERAK 2 2
A1 PERAK 3 3
B2 SELANGOR 4 1
B2 SELANGOR 5 2
C3 PAHANG 6 1
C3 PAHANG 7 2
C3 PAHANG 8 3
D4 KELANTAN 9 1

Anda boleh melakukannya dengan menggunakan SELECT STATEMENT berikut :

SELECT A.BATCH_NO,
DECODE(ROWNUM - min_seqno,0, 1, ROWNUM + 1 - min_seqno) AS seqno
FROM (SELECT * FROM BATCHLIST ORDER BY BATCH_NO) A,
(SELECT BATCH_NO, MIN(rownum) min_seqno
FROM (SELECT * FROM BATCHLIST ORDER by BATCH_NO)
GROUP BY BATCH_NO
ORDER BY BATCH_NO) B
WHERE A.BATCH_NO = B.BATCH_NO
ORDER BY A.BATCH_NO;