Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: Enumerate SQL query result

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?

like image 815
Adam Matan Avatar asked Jul 08 '15 05:07

Adam Matan


People also ask

What is SQL enumerate?

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

What is Unnest in PostgreSQL?

Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows. PostgreSQL offers unnest() function.

How do I get unique records in PostgreSQL?

Removing duplicate rows from a query result set in PostgreSQL can be done using the SELECT statement with the DISTINCT clause. It keeps one row for each group of duplicates. The DISTINCT clause can be used for a single column or for a list of columns.

How do I return a query in PostgreSQL?

To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.


1 Answers

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;
like image 75
Brad Ruderman Avatar answered Oct 20 '22 21:10

Brad Ruderman