Menambahkan No Urut Pada Hasil Sebuah Query

Terkadang kita perlu menambah no urut pada hasil query kita, untuk memenuhi hal ini, kita dapat memanfaatkan window functions [1].


Contoh : 

SELECT
  row_number() OVER (ORDER BY
  tn.ord ASC,
  CASE
    WHEN s.id_jenis_soal = 1 THEN 1
    WHEN s.id_jenis_soal = 2 THEN 3
    WHEN s.id_jenis_soal = 3 THEN 2
  END ASC,
  sn.nomor_soal ASC
) as no_soal,
  tn.id_naskah AS set_id,
  n.nama AS set_name,
  s.id_soal AS question_id,
  s.isi AS isi,
  s.gambar AS image,
  s.id_jenis_soal AS question_type,
  s.id_tipe_jawaban,
  ps.id_paragraph AS paragraph_id,
  ps.isi AS paragraph,
  ps.gambar AS gambar_paragraph,
  (
    select
        --count(sn2.id_soal)
        array_to_string(array_agg(td2.code || ': ' || n2.nama), ',')
    from
        testdoc td2,
        testdoc_naskah tn2,
        naskah n2,
        soal_naskah sn2
    where
        td2.id <> tn.testdoc_id AND
        td2.id = tn2.testdoc_id AND
        tn2.id_naskah = n2.id_naskah AND
        regexp_replace(trim(n2.nama), '[a-zA-Z]+$', '') <> regexp_replace(trim(n.nama), '[a-zA-Z]+$', '') AND
        n2.id_naskah = sn2.id_naskah AND
        sn2.id_soal = s.id_soal
  ) as nother
FROM
  testdoc_naskah tn,
  naskah n,
  soal_naskah sn,
  soal s LEFT JOIN paragraph_soal ps ON
    s.id_paragraph = ps.id_paragraph
WHERE
  tn.testdoc_id = 910 AND
  tn.id_naskah = n.id_naskah AND
  n.id_naskah = sn.id_naskah AND
  sn.id_soal = s.id_soal
ORDER BY
  tn.ord ASC,
  CASE
    WHEN s.id_jenis_soal = 1 THEN 1
    WHEN s.id_jenis_soal = 2 THEN 3
    WHEN s.id_jenis_soal = 3 THEN 2
  END ASC,
  sn.nomor_soal ASC

Referensi

1. Window Functions, http://www.postgresql.org/docs/9.1/static/functions-window.html