| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-21-11, 00:01
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 3
|
|
|
Query to return top 3 bidders without dupes?
|
|
I'll start by saying I am a complete rookie at this and have taught myself what little I know by searching the Internet.
In short, I have an online auction where the top three bidders will "win."
I need to be able to order the quesry results by top three bids without including more than one bid from the same bidder. I'm stuggling with making it work and selecting the max bids for each bidder.
For example, the database has the following bids in it:
Jack White 30
John Smith 32
Steve Jones 35
Bob Wilson 40
John Smith 45
I need the results to be:
John Smith 45
Bob Wilson 40
Steve Jones 35
But I'm getting strange results like:
John Smith 32
Bob Wilson 40
Steve Jones 35
My query is:
$select = mysql_query("SELECT First,Last,Bid FROM Bids GROUP BY First,Last ORDER BY Bid DESC LIMIT 3") or die(mysql_error());
Any help to get this to work correctly? Thanks
|
|
09-21-11, 04:15
|
|
Jaded Developer
|
|
Join Date: Nov 2004
Location: out on a limb
Posts: 9,250
|
|
why do you need a group by clause?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton 
|
|
09-21-11, 12:13
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
|
|
|
|
PHP Code:
$select = mysql_query("SELECT First,Last,Bid FROM Bids GROUP BY First,Last ORDER BY Max(Bid) DESC LIMIT 3") or die(mysql_error());
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
09-21-11, 12:51
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
pat, you know as well as i do that you cannot sort by an expression that isn't included in the SELECT clause
surely you meant to have Max(Bid) in the SELECT clause, and not just Bid
|
|
09-21-11, 13:26
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
|
|
Do I get half credit ??? 
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
09-21-11, 13:53
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by Pat Phelan
Do I get half credit ??? |
depends if your query actually produces the desired results
consider this data
tom 45
tom 43
tom 41
dick 39
dick 37
harry 42
harry 40
todd 48
todd 44
your query would give
tom 45
dick 39
harry 42
todd 48
however, the "top three bids without including more than one bid from the same bidder" would be
todd 48
tom 45
harry 42
|
|
09-21-11, 16:27
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 3
|
|
So is it as easy as adding MAX up front in SELECT?
|
|
09-21-11, 16:31
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
|
|
I think that you should be Ok if you use
PHP Code:
$select = mysql_query("SELECT First, Last, Max(Bid)
FROM Bids
GROUP BY First, Last
ORDER BY Max(Bid) DESC
LIMIT 3") or die(mysql_error());
Unfortunately I don't have anything handy to test it with, so you'll have to try it and see if the results are what you want or not.
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
09-21-11, 18:11
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 3
|
|
That resulted in the first and last names populating the results table but the bid column was blank. 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query to return top 3 bidders without dupes?
