WITH final AS ( SELECT 'a' AS gname, 'b' AS pname, 'c' AS name, 1 AS gid, 2 AS pid, 3 AS did, 3 AS inp UNION SELECT 'a1' AS gname, 'b1' AS pname, 'c1' AS name, 11 AS gid, 21 AS pid, 31 AS did, 3 AS inp )
Now I want to write a query depending on the inp column value
SELECT (CASE final.inp WHEN 1 THEN (SELECT DISTINCT(final.gid) AS id, final.gname as name FROM final ORDER BY final.gname) WHEN 2 THEN (SELECT DISTINCT(final.pid) AS id, final.pname as name FROM final WHERE final.gid = $1 ORDER BY final.pname) WHEN 3 THEN (SELECT DISTINCT(final.did) AS id, final.name as name FROM final WHERE final.pid = $1 ORDER BY final.name) END) FROM (%s) final;
I know this is not a correct way of writing it.
But this will give you an idea what I am trying to do.
inp = 1 -> get unique gid’s and gname and order by gname
inp = 2 -> get unique pid’s and pname where gid = and order by pname.
I want to write this logic in psql.
I know case
When can have only one column so I have to create two cases for a single
final.inp=1 for name and id.
I ain’t able to write this logic because of this part
(SELECT DISTINCT(final.pid) AS id, final.pname as name FROM final WHERE final.gid = $1 ORDER BY final.pname)
WHERE ORDER BY <— and passing the same final value to it.
I am not able to implement it. Can anyone help?
select distinct case when inp = 1 then gid when inp = 2 then pid when inp = 3 then did end new_id, case when inp = 1 then gname when inp = 2 then pname when inp = 3 then name end new_name from final order by case when inp = 1 then gname when inp = 2 then pname when inp = 3 then name end
Answered By – Kadet
Answer Checked By – Candace Johnson (GoLangFix Volunteer)