Select entire row on group by aggregation

Question

I'm having some struggle with something that should be a simple SQL query.

This is my initial database schema:

Also prepared the example in SQLFiddle

The query I've ended up with is:

select b.ad_id, b.auction_id, max(b.amount) as max, max(b.created_at) created_at from bid b where b.user_id = '601' group by b.ad_id, b.auction_id

But in the result, I need the whole row from the bid table:

select b.id, b.ad_id, b.auction_id, max(b.amount) as max, max(b.created_at) created_at from bid b where b.user_id = '601' group by b.ad_id, b.auction_id

Which fails with: [42803] ERROR: column "b.id" must appear in the GROUP BY clause or be used in an aggregate function Position: 16. Cannot add the id field in the GROUP BY clause, because it will add some extra rows I don't need.

What I need is to select from the bid table the highest record (amount field) grouped by auction_id and ad_id.

I think I need to make some self inner join or subselect but right now I'm not able to write the SQL.

Please describe the logic you want to implement.

Gordon Linoff
– Gordon Linoff

2019-12-05 15:41:53 +00:00
Commented Dec 5, 2019 at 15:41 — Gordon Linoff
– Gordon Linoff, Commented Dec 5, 2019 at 15:41
Updated with more info

alxsimo
– alxsimo

2019-12-05 15:48:23 +00:00
Commented Dec 5, 2019 at 15:48 — alxsimo
– alxsimo, Commented Dec 5, 2019 at 15:48

Haleemur Ali · Accepted Answer · 2019-12-05 16:00:49Z

What I need is to select from the bid table the highest record (amount field) grouped by auction_id and ad_id

Take a look at DISTINCT ON in the docs. Your desired result would be obtained by the following query.

select DISTINCT ON (b.ad_id, b.auction_id) b.id, b.ad_id, b.auction_id, b.amount b.created_at from bid b where b.user_id = '601' ORDER BY b.ad_id, b.auction_id, b.amount DESC

This looks like what I need. Please let me check. Gave you +1

Gordon Linoff · Accepted Answer · 2019-12-05 15:43:28Z

If you want the most recent row for each auction for the given user, then you can use a correlated subquery to filter:

select b.* from bid b where b.user_id = '601' and b.created_at = (select max(b2.created_at) from bid b2 where b2.auction_id = b.auction_id and b2.user_id = b.user_id );

This seems like a sensible interpretation of what you want. I don't know if ad_id is needed.

Collectives™ on Stack Overflow

Select entire row on group by aggregation

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related