PostgreSQL - GROUP LIMIT

Salam semua,

Bersawang dah blog ni... lama gila x menulis.

nak share bende baru bagi aku. sebelum ni tak pernah guna advance(sebab aku reti guna select,insert,update,delete shj) SQL.

Pengunaan nya, aku nak group kan mengikut column dan nak limit 3 result teratas sahaja utk setiap grouping.

lepas tanya sifu postgres(Helmi), dia suggest pakai menatang ni.

WITH table_name as (
 ..... complicated queries with join & condition etc......
)  

SELECT
  * 
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS r,
    t.*
  FROM
    table_name t) x
WHERE
  x.r <= 3;

As postgres support WITH, so aku prefer asingkan main complicated queries dgn GROUP LIMIT kan. Semua select/join and where condition buat dlm WITH.

Enjoy.