Skip to content

Instantly share code, notes, and snippets.

@tototoshi

tototoshi/rownum.sql

Created December 26, 2012 01:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tototoshi/4376938 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tototoshi/4376938 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. tototoshi created this gist Dec 26, 2012.
    34 changes: 34 additions & 0 deletions rownum.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,34 @@
    -- 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)