CODEWITHSUNDEEP
postgresql
user1411837
user1411837

Reputation: 1564

Postgresql - Syntax error - while using with table_name as

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.

Upvotes: 3

Views: 90

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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

Upvotes: 4

Related Questions