CODEWITHSUNDEEP
postgresql
Vikram Singh Shekhawat
Vikram Singh Shekhawat

Reputation: 764

Order by not working on calculated columns if order by applied on multiple columns

I am using postgresql version 9.6. I want to apply order by on a calculated column with one existing column. If I apply order only on calculated column then it working fine but it is throwing error when one more column is added in order by clause.

Error is: 

ERROR:  column "column_name" does not exist

Here are the queries:

Create Table:

CREATE TABLE "student" (
    "age" numeric(2) NOT NULL ,
    "name" varchar(128) NOT NULL);

Insert Data:

insert into student values(22, 'Vikram');
insert into student values(12, 'Bhagirath');
insert into student values(12, 'SKR');

Query:

Select *, 
       CASE WHEN age>18 then 'Adult' ELSE 'MINOR' end  as category 
from student 
order by category;

The above query is working fine. If I add name to order by clause then it throw error:

Select *, 
       CASE WHEN age>18 then 'Adult' ELSE 'MINOR' end  as category 
from student order by (category,name);

Error:

ERROR:  column "category" does not exist

Upvotes: 0

Views: 75

Answers (1)

user330315
user330315

Reputation:

Remove those parentheses around the columns for the group by:

Select *, CASE WHEN age>18 then 'Adult' ELSE 'MINOR' end  as category 
from student 
order by category, name;

Online example: https://rextester.com/BHQ36816

Upvotes: 1

Related Questions