0

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.

Crowley
  • 67
  • 7

1 Answers1

2
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

Community
  • 1
  • 1
Radek Postołowicz
  • 4,506
  • 2
  • 30
  • 47