I have this situation where I can only select the fields that I want to group by..... so I could find the largest item within the group.
For example, bid price 2.65 is the largest so I want to write a query to chose it out of the group.
This query below does the job and gets what I wanted, however it doesn't include all the fields that I wanted, it only includes fields such
as seller, iid, starttime, endtime, maxofbidprice
Code:
SELECT bid.seller, bid.iid, bid.starttime, auction.endTIME, Max(bid.bidprice) AS MaxOfbidprice, Count(*) AS nBIDsFROM auction INNER JOIN bid ON (auction.[startTIME] = bid.starttime) AND (auction.iid = bid.iid) AND (auction.seller = bid.seller)
WHERE (((bid.bidtime)<[auction].[endTIME]) AND ((bid.bidprice)>=[auction].[minbid]) AND ((Now())>=[auction].[endTIME]))
GROUP BY bid.seller, bid.iid, bid.starttime, auction.endTIME;
So my solution was to join to other table to include other fields, and this is the code below, unfortunately with this code it doesn't get the largest item from the group.
The reason is because the second user (user_1) table is join with the bid table instead of the auction, but since I want to get buyer I have to join the bid table and not the auction table because the
auction table doesn't have the bidder field.
Because of the join to the bidder table, this caused it not to get the largest item in the group, however I could get the other fields, but the result of the query is not accurate anymore.
Code:
SELECT winningBIDtwo.winningBID.bid.seller AS Expr1, user.uname, winningBIDtwo.bid.bidder, USER_1.uname, winningBIDtwo.winningBID.bid.iid AS Expr2, item_type.itemname, winningBIDtwo.bidSTARTtime, auction.starttime AS auctionSTARTtime, winningBIDtwo.auctionENDtime, winningBIDtwo.bid.bidtime, auction.minbid, winningBIDtwo.bid.bidprice
FROM item_type INNER JOIN (auction INNER JOIN ([USER] AS USER_1 INNER JOIN ((SELECT winningBID.seller, bid.bidder, winningBID.iid, winningBID.starttime AS bidSTARTtime, winningBID.endTIME AS auctionENDtime, bid.bidtime, bid.bidprice
FROM (SELECT bid.seller, bid.iid, bid.starttime, auction.[endTIME], max(bid.bidprice), count(*) AS nBIDs
FROM auction INNER JOIN bid ON (auction.[startTIME]=bid.starttime) AND (auction.iid = bid.iid) AND (auction.seller = bid.seller)
WHERE bid.bidtime<auction.[endTIME] And bid.bidprice>=auction.minbid And Now()>=auction.[endTIME]
GROUP BY bid.seller, bid.iid, bid.starttime, auction.[endTIME]) AS winningBID
INNER JOIN BID ON (winningBID.seller = BID.seller) AND (winningBID.iid = BID.iid) AND (winningBID.starttime = BID.STARTtime)) AS winningBIDtwo INNER JOIN [user] ON winningBIDtwo.seller = user.uid) ON USER_1.uid = winningBIDtwo.bidder) ON (auction.starttime = winningBIDtwo.bidSTARTtime) AND (auction.iid = winningBIDtwo.iid) AND (auction.seller = winningBIDtwo.seller)) ON item_type.iid = winningBIDtwo.iid;
Here is the fields I wanted but I want the largest value from the group too, how can I do that?