Grouped LIMIT in PostgreSQL: show the first N rows for each group

rownum.sql

-- http://stackoverflow.com/questions/1124603/grouped-limit-in-postgresql-show-the-first-n-rows-for-each-group
-- http://www.postgresql.jp/document/9.2/html/tutorial-window.html

CREATE TABLE empsalary (
  depname varchar(10) not null
  , empno integer not null
  , salary integer not null
);

INSERT INTO empsalary (depname, empno, salary) VALUES ('develop', 11, 5200);
INSERT INTO empsalary (depname, empno, salary) VALUES ('develop', 7, 4200);
INSERT INTO empsalary (depname, empno, salary) VALUES ('develop', 9, 4500);
INSERT INTO empsalary (depname, empno, salary) VALUES ('develop', 8, 6000);
INSERT INTO empsalary (depname, empno, salary) VALUES ('develop', 10, 5200);
INSERT INTO empsalary (depname, empno, salary) VALUES ('personnel', 5, 3500);
INSERT INTO empsalary (depname, empno, salary) VALUES ('personnel', 2, 3900);
INSERT INTO empsalary (depname, empno, salary) VALUES ('sales', 3, 4800);
INSERT INTO empsalary (depname, empno, salary) VALUES ('sales', 1, 5000);
INSERT INTO empsalary (depname, empno, salary) VALUES ('sales', 4, 4800);

example=# select * from (
            select depname, empno, salary, row_number() over (partition by depname order by salary) as rownum from empsalary
          ) tmp
          where rownum < 3;

  depname  | empno | salary | rownum
-----------+-------+--------+--------
 develop   |     7 |   4200 |      1
 develop   |     9 |   4500 |      2
 personnel |     5 |   3500 |      1
 personnel |     2 |   3900 |      2
 sales     |     3 |   4800 |      1
 sales     |     4 |   4800 |      2
(6 rows)

Thank you man! Great trick.

Thank you, this idea just saved me lots of time :)

Fantastic!

It's worth noting the new Lateral Join approach explained in Charles' blog: http://charlesnagy.info/it/postgresql/group-by-limit-per-group-in-postgresql

Thanks !

thanks

This query will fail to limit the group number to only 2 if the rownumber() is same for two you will get both of those returned

Thanks!

Thanks!

Thanks!

Thanks!