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.

Lukasz Szozda · Accepted Answer · 2018-06-20 12:16:24Z

4

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

answered Jun 20, 2018 at 12:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Collectives™ on Stack Overflow

Postgresql - Syntax error - while using with table_name as

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