Postgresql - Syntax error - while using with table_name as

Question

Not sure what needs to be done , i am using pgadmin3 but regardless of the GUI i keep getting the same error .

Below is my query :

 with base_table as 
(select row_number() over (partition by p.customer_id order by p.payment_date ) as early_order,
        row_number() over (partition by p.customer_id order by p.payment_date desc) as last_order
    from payment p)

Syntax error at the end of input

I have even tried adding a semicolon at the end, doesnt work :

with base_table as 
(select row_number() over (partition by p.customer_id order by p.payment_date ) as early_order,
        row_number() over (partition by p.customer_id order by p.payment_date desc) as last_order
    from payment p);

Syntax error near ";"

The inner query (which is below) works just fine :

select row_number() over (partition by p.customer_id order by p.payment_date ) as early_order,
        row_number() over (partition by p.customer_id order by p.payment_date desc) as last_order
    from payment p

Its only when i use with table_name as , that the errors start to show up.

Answer 1

You need to add main select:

with base_table as (
 select row_number() over(partition by p.customer_id order by p.payment_date) as early_order,
        row_number() over(partition by p.customer_id order by p.payment_date desc) as last_order
 from payment p)
SELECT *
FROM base_table