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?
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.
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With