Page 1 of 1

Ordering by date for multiple sub queries?

Posted: Fri Aug 13, 2010 4:17 pm
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!

Re: Ordering by date for multiple sub queries?

Posted: Fri Aug 13, 2010 5:41 pm
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

Re: Ordering by date for multiple sub queries?

Posted: Fri Aug 13, 2010 6:59 pm
by GeXus
It would have to be done without any auction id's.... at this point we don't know what auction ids

Re: Ordering by date for multiple sub queries?

Posted: Fri Aug 13, 2010 7:02 pm
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.