How to generate a certain table on the fly in a with statement?

user_1065175 0
Viewed 307

In a query I want to order by a column which has values like 'Foo', 'Bar', 'Baz'. Somehow I have to define an order. What I did was to create a small temporary table like so:

n     i
'Foo' 1
'Bar' 2
'Baz' 3

I join the tables in my original query with this one on the column 'n', and then order by the column 'i'.

This works. But can I instead of creating a temporary table generate this table on the fly in a with statement for example and then use it? I know generate_series, but can I use that? Or is there another way?

postgresql
2 Answers

You can use a VALUES clause:

with temp (n,i) as (
  values 
    ('Foo',1),
    ('Bar',2) ,
    ('Baz',3)
)
select *
from temp
order by i;

You don't really need a CTE for that, you ca put the VALUES clause into the FROM as well:

select *
from (
  values 
    ('Foo',1),
    ('Bar',2),
    ('Baz',3)
) as temp(n,i);
user_3303150

If you want to prescribe your order, you can easily use a WITH statement, like so:

WITH mytable AS (
    SELECT 'Foo' AS n, 1 AS i
    UNION SELECT 'Bar', 2
    UNION SELECT 'Baz', 3
)
SELECT *
FROM mytable
user_63890100
Related