SQL Subquery Help

[MartinSmith]

I am currently writing a basic charity online auction asp application for our intranet. All the bids are in a bid table which has fields for

BidID, ItemID, BidTime, and BidValue.

The ItemID joins to an Item table to get the ItemName.
The BidderID joins to a Bidder table to get the BidderName.

I am trying to write a query that will generate a list of all items, the current winning bidder and the Winning bid. But I am struggling with this.

I can get a list of WinnningBids and ItemIDs with the below,

SELECT MAX(BidValue) AS MaxBid, ItemID, BidderID
FROM Bid
GROUP BY ItemID, BidderID

but if 2 people have bid the same it must go to the one with the earlier bid time (and I must allow same bids so it can go to the second person if the first person claims not to have bidded!)

Any ideas how I could go about this in one query?

[si_the_geek]

Something like this should do the trick..

SELECT MAX(BidValue) AS MaxBid, ItemID, BidderID
FROM Bid

WHERE <datefield> = (
   SELECT Min(<datefield>)
   FROM Bid Bid2
   WHERE ItemID = Bid.ItemID
   AND BidValue = Bid.BidValue  
   )

GROUP BY ItemID, BidderID

[Ecniv]

SELECT MAX(BidValue) AS MaxBid, ItemID, BidderID, datetimebidded
FROM Bid
GROUP BY Desc ItemID,datetimebidded, BidderID

I think that will return in the right order ....

As to the cancellation of a bid, you'd need to figure out how to do that. Perhaps a Boolean field and then you can filter on that too.


Vince

[MartinSmith]

Thanks - but I still haven't got it exactly

Si.....

I tried.....

SELECT MAX(BidValue) AS MaxBid, ItemID, BidderID
FROM Bid
WHERE BidTime =
(SELECT MIN(BidTime)
FROM Bid Bid2
WHERE ItemID = Bid.ItemID AND BidValue = Bid.BidValue)
GROUP BY ItemID, BidderID


This Returned.......

2200     2      ANS
900      1      MSM
200      2      MSM


I have no idea where that 200 has come from though - Help!

Ecniv

I couldn't get your SQL as written to work...

Did you mean this?

SELECT MAX(BidValue) AS MaxBid, ItemID, BidderID,
BidTime
FROM Bid
GROUP BY BidTime, ItemID, BidderID
ORDER BY BidTime DESC

If so this unfortunately returns every row in the table as the time is different for each row.

[Ecniv]

Yeah I meant that (sorry).

And as to the returned all records .. they are ordered though ?

Your question said you wanted the first unless the first says they didn't bid then you need the second. This should list the top ten (say) in order of bids which yiu can produce.

Then highlight the top bid and if they say no - you can disable and give to the second top most bid .. etc.



Vince

[MartinSmith]

Aah I see!

I probably didn't make the question clear enough. The purpose of the query is to display a list of all current Bids, Items, and Values whilst the auction is running.

I know that I could just get all the data, order it by MaxBid and Time and then just loop through it and do the processing on the page itself but I am sure there must be a way of doing it that just brings back the relevant results in the first place.

If someone claims not to have bidded then I will just delete that bid record anyway so if I manage to get the first query working then the results it brings back should still be correct.

[Ecniv]

I see what you need I think.

You want the grab the max bid with the min bid time.

Hmm.
Ok, roughed out the sort of thing you want... try this...

SELECT Max(bid.BidValue) AS MaxOfBidValue, bid.ItemID, bid.BidderId, Min(bid.BidTime) AS MinOfBidTime
FROM bid
GROUP BY bid.ItemID, bid.BidderId
ORDER BY Max(bid.BidValue) DESC , Min(bid.BidTime);

This was run on a table bid of :

BidID	ItemID	BidderId	BidTime	BidValue
1	1	1	01/01/01 00:00:10	10
2	1	2	01/01/01 00:00:12	11
3	1	1	01/01/01 00:00:20	15
4	1	3	01/01/01 00:00:15	15

and gave the results :

MaxOfBidValue	ItemID	BidderId	MinOfBidTime
15	1	1	01/01/01 00:00:10
15	1	3	01/01/01 00:00:15
11	1	2	01/01/01 00:00:12

Vince

[si_the_geek]

oops, slight mistake.. the BidValue in the subquery should have been "MaxBid", but that isn't allowed.. this should work:

SELECT MAX(BidValue) AS MaxBid, ItemID, BidderID
FROM Bid
WHERE BidTime =
   (SELECT MIN(BidTime)
    FROM Bid Bid2
    WHERE ItemID = Bid.ItemID 
    AND BidValue = 
         (SELECT Max (BidValue)
          FROM Bid3 
          WHERE ItemID = Bid.ItemID)
   )
GROUP BY ItemID, BidderID

not too pretty tho', it should be possible to make it look nicer (and maybe a bit quicker), but my brain is dead at the mo!

[MartinSmith]

Thanks very much both of you.

That looks to be exactly what I need.

I really do struggle when it comes to subqueries for some reason!

Oh, and please excuse the lateness of my thanks but for some reason I didn't get any notifications about replies!?

[stpet]

Try this. May be it would also work for you:
select top 1 bidid from Bids WHERE BidValue=(SELECT Max(b.BidValue) FROM Bids b)
ORDER BY BidTime Asc