My Auctions
CREATE TABLE Auction (
id SERIAL PRIMARY KEY,
owner_id INTEGER REFERENCES Registered(user_id),
title VARCHAR(50) NOT NULL,
starting_bid NUMERIC(10,2) NOT NULL CHECK (starting_bid >= 0),
starting_time TIMESTAMP NOT NULL,
ending_time TIMESTAMP NULL CHECK (starting_time < ending_time),
status_id INTEGER REFERENCES AuctionStatus(id),
winner_id INTEGER REFERENCES Registered(user_id) NULL,
highest_bid NUMERIC(10,2) NOT NULL
);
My Bids
CREATE TABLE Bid (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES Registered(user_id),
auction_id INTEGER REFERENCES Auction(id),
bid_value NUMERIC(10,2) CHECK (VALUE > 0),
creation_date TIMESTAMP NOT NULL,
valid BOOLEAN DEFAULT TRUE NOT NULL
);
I want a user to be able to list all the auctions where he has bid + his highest bid in each auction.
I started with
SELECT auction.id, auction.title, auction.highest_bid, bid.bid_value
FROM auction
JOIN bid
ON auction.id = bid.auction_id
WHERE bid.owner_id = :userID AND bid.active = TRUE AND auction.status_id = 1
to list all of his bids but I'm having trouble moving forward from here to get the highest bid of the user for each auction.
SELECT distinct on (auction.id) auction.id, auction.title, auction.highest_bid, bid.bid_value
FROM auction
JOIN bid
ON auction.id = bid.auction_id
WHERE bid.owner_id = :userID AND bid.active = TRUE AND auction.status_id = 1
ORDER BY auction.id, bid.bid_value desc;
For more info see:
Select first row in each GROUP BY group?
and
"DISTINCT Clause" paragraph from http://www.postgresql.org/docs/current/static/sql-select.html