select a specific row for each group according to a custom logic

Question

I have the following table:

create table  test_table (
    id serial primary key,
    store text,
    price int,
    id_2 text);
insert into test_table (store, id_2, price) values
('a', '11', 100),
('b', '22', 120),
('c', '33', 150),
('d', '33', 120),
('b', '11', 90),
('d', '44', 240),
('a', '22', 80),
('b', '55', 140),
('c', '11', 150) returning *;

select * from test_table tt group by id_2, id order by id_2;

I want to create a view/sub-table where for each group by id_2 (grouped_rows), I pick one row according to a custom logic, something like:

if 'a' in grouped_rows.store:
  select row where a is present
else if 'c' in grouped_rows.store:
  select row where c is present
...

As I understand, window functions work with the values from a partition (same grouping as the previous group-by)

select store, id_2, count(id_2)  over (partition by id_2) as counts
from test_table tt  order by counts desc;

So can I use a custom window function to apply the previous logic, or are any other way to achieve this? until now, i haven't found a way to use a window function to achieve something like this.

As an extra, is it possible to build the selected row, for example, adding price, where the price would be the min() of the group (which doesn't have to be from the same row selected by store, but it's from the same grouped_rows).

if 'a' in grouped_rows.store:
  select row where 'a' is present, select min(grouped_rows.price)
else if 'c' in grouped_rows.store:
  select row where 'c' is present, select min(grouped_rows.price)
...

no you can't customize window functions, without changing the source code — nbk
– nbk, Commented Dec 5, 2022 at 18:45

Stefanov.sm · Accepted Answer · 2022-12-05 19:15:36Z

2

You may try to use first_value() window function with proper custom order by in the window definition. Yet using distinct on with custom ordering solves your problem easier and better. You can still use min() for the price.

select distinct on (id_2) * 
from test_table 
order by id_2, 
  case store when 'a' then 1 when 'c' then 2 end nulls last;

Please note the second (case) expression in the order by clause. It is modeling the rule "If there is a row with store = 'a' in the same id_2 group of rows then take it, else if there is a row with store = 'c' in it then take it, else take any row of the group".

id	store	price	id_2
1	a	100	11
7	a	80	22
3	c	150	33
6	d	240	44
8	b	140	55

With min_price extra:

select distinct on (id_2) *,
  min(price) over (partition by id_2) as min_price 
from test_table 
order by id_2,
  case store when 'a' then 1 when 'c' then 2 end nulls last;

id	store	price	id_2	min_price
1	a	100	11	90
7	a	80	22	80
3	c	150	33	120
6	d	240	44	240
8	b	140	55	140

edited Dec 5, 2022 at 19:15

answered Dec 5, 2022 at 19:07

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

2 Comments

matrs Over a year ago

Thanks!. I don't have much experience with sql, so let me ask this basic question: how these 1, 2,etc. in the case expression translates to pick the row that i want? I understand those aren't indices of the rows, so how those number translate to pick the row where the condition is true?

Stefanov.sm Over a year ago

This is how distinct on works. It orders all the rows by the list of expressions in the brackets and then picks the row that happens to be first from each group with the same values. This is not what you need so you have to further order by within this group so that the first row is the needed one. The case expressions returns 1 for the first choice, 2 (or anything > 1) for the second choice and so on. distinct on picks the first, i.e. smallest of them. The particular values (1, 2, ...) have no other meaning except that.

Collectives™ on Stack Overflow

select a specific row for each group according to a custom logic

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related