Ordering by date for multiple sub queries?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Ordering by date for multiple sub queries?

Post by GeXus »

Okay, I'm a little confused here on the best way to approach this. Here's a simplified version of what I have..


Auction site
Tables: users, bids

Let's assume an auction is over. To determine who the winner is, we do this
SELECT user_id FROM bids WHERE auction_id = '1'
ORDER BY bid_date DESC
LIMIT 1
This is simple because we know the auction_id... Now what if we want to list all won auctions for a given user? I have no idea... because each auction needs to be ordered by bid_dt DESC and limit 1... I've tried with a sub query, but no luck......

Any suggestions? Thank you!
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Ordering by date for multiple sub queries?

Post by JakeJ »

Use max(date) and multiple auction_id's in the WHERE clause.

Code: Select all

SELECT user_id, max(date) as date FROM bids WHERE auction_id = 1 || 2 || 3|| 4 || 5|| 6
ORDER BY auction_id
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Re: Ordering by date for multiple sub queries?

Post by GeXus »

It would have to be done without any auction id's.... at this point we don't know what auction ids
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Ordering by date for multiple sub queries?

Post by JakeJ »

Assuming you'll be doing this in php, you can assign auction_id's as variables and put them in the query that way.
Post Reply