[SOLVED] select max values

[AdRock]

I have a table where the max value might be the same

 

How do i query getting the max value anmd if there are more than one row with the same max value, select them both?

 

At the moment it selects the lowest id with the max value

SELECT id,item, bidder_email, bid FROM auctions WHERE sold='n' AND bid=(SELECT MAX(bid) FROM auctions) GROUP BY item ASC

[minus4]

wouldn't changing your = to IN () bring back your multiple results, but i dont see how you can get 2 users to bid the same amount ???? and both win!

 

if you somehow do then it goes to the person that bid last

[AdRock]

This is a silent auction that i'm working on not like ebay or similar

 

That made sense using IN but it still doesn't work.  Have i got the query right?

 

I decided to use a seperate table for the item and another for the bidders

 

SELECT a.item as item, b.email as email, b.bid as bid FROM auctions a, bidders b WHERE a.id=b.auction_id AND bid IN (SELECT MAX(bid) FROM bidders) GROUP BY item

[minus4]

looks okay apart from bid if you have bid in both tables then it should be b.bid IN ()

also why don't it work???? that don't really explain, did it error, or not bring back results?

 

did you run an explain? to see what tables return what results!

 

also u don't really want all the bids, don't u want the highest bid, done for that auction ID???

why don't you  simple select then order by bidamount ASC and limit to 1, this will give you the same results right?

[kickstart]

Hi

 

That subselect would bring back the max bid, irrespective of what it was a bid for.

 

Try:-

 

SELECT a.item as item, b.email as email, b.bid as bid FROM auctions a, bidders b WHERE a.id=b.auction_id AND bid IN (SELECT MAX(bid) FROM bidders c WHERE c.auction_id = b.auction_id) GROUP BY item

 

All the best

 

Keith

[AdRock]

I'll explain it a bit more as everytthing i try just gives back one row which has the max value but also is one of the earlier ids in the table

 

I have a table with info on an auction item such as id, title, desc and wether it's sold or not

 

The other table is for bidders with email address and their bid etc

 

The queries all work but only outputs one result when there are two bids for the same amount.

 

Is it not possible to output both bids displaying the same bid price and the two different email addresses?

 

I did think of using HAVING and somehow have one of the conditions being the MAX value but it didn't work

[kickstart]

Hi

 

The select I gave you above should give you what you want if the bids are the same.

 

All the best

 

Keith

[AdRock]

I found something that i was able to use and it works

 

SELECT b.*
FROM bidders b
INNER JOIN
    (
    SELECT auction_id, MAX(bid) AS MaxBid
    FROM bidders GROUP BY auction_id
    ) groupedtt ON b.auction_id= groupedtt.auction_id AND b.bid = groupedtt.MaxBid

 

It shows all rows that have the max values but what I can't get it to do is output the item field which is in the auctions table.  So far, all the query looks at is the bidders table.

 

I want to have the item displayed next to the email and the bid amount but when i tried to add the auction table, it duplicated rows even if i used distinct.

 

Out of the 3 columns i want to display, the item column is empty, the bidders email and bid are displayed.

 

Is there a way i can select the item field in the auction table that is linked by the auction.id = bidders.auction_id

 

 

 

EDIT: FIXED AND SOLVED

 

thanks for all your help

[fenway]

FIXED AND SOLVED -- how?