PostgreSQL: Enumerate SQL query result

Question

Consider the following SQL query and response:

CREATE TEMPORARY TABLE dreams (name text, type text);
INSERT INTO dreams VALUES ('Monkey', 'nice');
INSERT INTO dreams VALUES ('Snake', 'Not nice');
INSERT INTO dreams VALUES ('Donkey', 'nice');
INSERT INTO dreams VALUES ('Bird', 'nice');

SELECT name from dreams WHERE type='nice' ORDER BY name;
  name
--------
 Bird
 Donkey
 Monkey
(3 rows)

I would like to enumerate the results by the order of appearance, regardless of any existing ids, for convenience. The expected result should be something a-la:

SELECT <magic_enumeration>, name from dreams WHERE type='nice' ORDER BY name;

 magic_enumeration |  name
-------------------+--------
 1                 | Bird
 2                 | Donkey
 3                 | Monkey
(3 rows)

Any ideas how to enumerate the query result by order of appearance?

Adam Matan · Accepted Answer · 2015-07-08 05:51:24Z

18

Try using row_number, which is a windowing function

SELECT row_number() OVER (ORDER BY name) sid, <-- magic enumeration!
       name 
  FROM dreams 
  WHERE type='nice' 
  ORDER BY name;

edited Jul 8, 2015 at 5:51

Adam Matan

138k155 gold badges414 silver badges584 bronze badges

answered Jul 8, 2015 at 5:38

Brad Ruderman

2,1832 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Enumerate SQL query result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related