Postgres get values by giving more priority to a certain column

Question

I have a pgsql table with data as below.

platform;party;code;value
----------------------------
Global;null;name;abc
Local;null;name;abc
Global;null;number;123
Global;null;email;def
Global;Jen;email;fhg
Global;Jack;email;ijk

How to write a query where for a party & platform value passed, it need's to fetch values that the party has & the platform values which the party doesn't have as code params, and if null is passed for party, it should fetch all records where platform value is given by user.

(a) i.e. if i pass party as 'Jen' and platform as 'Global' From the above records i should get the following as result:-

platform;party;code;value
----------------------------
Global;null;name;abc
Global;null;number;123
Global;Jen;email;fhg

(b) and if i pass 'Jack' and platform as 'Global' it should return :-

platform;party;code;value
----------------------------
Global;null;name;abc
Global;null;number;123
Global;Jack;email;ijk

(c) if only platform is passed as 'Global' is passed then it should return :-

platform;party;code;value
----------------------------
Global;null;name;abc
Global;null;number;123
Global;null;email;def

(d) if only platform is passed as 'Local' is passed then it should return :-

platform;party;code;value
----------------------------
Local;null;name;abc

P.S.- I am sorry for my bad english. Will edit with proper language soon.

Gordon Linoff · Accepted Answer · 2021-06-22 12:32:11Z

1

You can use distinct on

select distinct on (platform, (party is not null), code) t.*
from t
where platform = ? and
      (party = ? or party is null)
order by platform, (party is not null) desc, code;

This puts the non-NULL rows before the NULL rows.

edited Jun 22, 2021 at 12:32

answered Jun 22, 2021 at 11:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

N29 Over a year ago

I tried :- "select distinct on ( platform,party, code) e.* from test e where platform = 'GLOBAL' and (party = 'Jack' or party is null) order by platform, (party is not null ) desc, code;" But getting the following error ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on (party, platform, code) t.* from test t, with an arrow pointing at code

Gordon Linoff Over a year ago

@N29 . . . I fixed that.

N29 Over a year ago

thank you so much.. My knowledge in sql is a little weak. I am working on improving it

Collectives™ on Stack Overflow

Postgres get values by giving more priority to a certain column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related