CODEWITHSUNDEEP
postgresql
user2058002
user2058002

Reputation:

Joining the maximum of a column from another table

I created the following tables:

create table items (
    id serial primary key,
    store_id int,
);

create table bids (
    item_id int,
    amount int
);

I want to select all items from a certain store and include information about the highest bid:

select items.*, max(bids.amount) from items
    join bids on bids.item_id = items.id
where items.store_id = $store_id

I get the following error:

column "items.id" must appear in the GROUP BY clause or be used in an aggregate function

What am I doing wrong?

Upvotes: 0

Views: 111

Answers (3)

user330315
user330315

Reputation:

Maybe you wanted a window function?

select items.*, 
       max(bids.amount) over (partition by bids.item_id) as max_bid_for_item
from items
    join bids on bids.item_id = items.id
where items.store_id = $store_id

Upvotes: 1

wildplasser
wildplasser

Reputation: 44240

SELECT *
FROM items i
JOIN bids b ON b.item_id = i.id
WHERE i.store_id = $store_id
AND NOT EXISTS (
   SELECT *
   FROM bids nx                 -- No bid should exist
   WHERE nx.item_id = b.item_id -- ... for the same item
   AND nx.amount > b.amount     -- ... but with a higher price
   );

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79566

Your error message tells you what you're doing wrong. To use an aggregate function (max()), everything else in your SELECT clause must be in a GROUP BY clause.

SELECT items.*, MAX(bids.amount)
FROM items
JOIN bids ON bids.item_id = items.id
WHERE items.store_id = $store_id
GROUP BY items.id,items.store_id;

Upvotes: 0

Related Questions