Skip to content

Instantly share code, notes, and snippets.

@tototoshi
Created December 26, 2012 01:14
Show Gist options
  • Save tototoshi/4376938 to your computer and use it in GitHub Desktop.
Save tototoshi/4376938 to your computer and use it in GitHub Desktop.
Grouped LIMIT in PostgreSQL: show the first N rows for each group
-- 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)
@poshest
Copy link

poshest commented Jun 16, 2016

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

@kible-company
Copy link

Thanks !

@maishuguang
Copy link

thanks

@SwargamAvinash
Copy link

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

@Angatupyry
Copy link

Thanks!

@davidddp
Copy link

davidddp commented Mar 3, 2021

Thanks!

@rhonan
Copy link

rhonan commented Mar 9, 2021

Thanks!

@rafsaf
Copy link

rafsaf commented Oct 14, 2022

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment