GROUP by yet SELECT fields not within GROUP BY

[Dal Jeanis]

To simplify your life, you need to uniquely identify the winning Bid record itself. That's why you need an autokey BidID.



I'm going to assume that the BidIDs were assigned in ascending order, so that later BidIDs are higher than earlier ones. This assumption will be true except on initial conversion, may still be true then, and only matters if you have duplicate valid bids for the same amount in a particular auction.

We want the earliest valid bid for each auction that has the highest price of any valid bid for that auction.

Query1:
SELECT TB.AuctionID, Min(TB.BidID) As WinBID
FROM 
   tblBids AS TB 
   INNER JOIN 
   tblAuctions AS TA 
   ON TB.AuctionID = TA.AuctionID
WHERE TA.Endtime < Now()
  AND TB.BidPrice IN
   (SELECT MAX(TB2.BidPrice)
    WHERE TB2.AuctionID = TA.AuctionID
    AND TB2.BidTIME <= TA.endTIME
    AND TB2.BidPrice >= TA.minBiD)
GROUP BY TB.AuctioNID;

You can get the same result using the bidtime, but it will have multiple subselects and look much more complicated. Here's the code that should do it without that assumption... I hope:

Query1:  (alternate)
SELECT TB.AuctionID, TB.BidID As WinBID
FROM 
   tblBids AS TB 
   INNER JOIN 
   tblAuctions AS TA 
   ON TB.AuctionID = TA.AuctionID
WHERE TA.Endtime < Now()
  AND TB.BidPrice IN
   (SELECT MAX(TB2.BidPrice)
    WHERE TB2.AuctionID = TA.AuctionID
    AND TB2.BidTIME <= TA.endTIME
    AND TB2.BidPrice >= TA.minBiD)
AND TB.BidTime IN 
   (SELECT MIN(TB3.BidTime)
    WHERE TB3.AuctionID = TA.AuctionID
    AND TB3.BidTIME <= TA.endTIME
    AND TB3.BidPrice >= TA.minBiD
    AND TB3.BidPrice = TB.BidPrice);

Now, each of those queries uniquely identifies the winners (if any). All you have to do is use a simple join to the tblAuction record on one side and the tblBids record on the other, and you can report anything you want.

SELECT TA.AuctionID, TA.Seller, TA.iid, TB.BidID, TB.Bidder, TB.BidTime, TB.BidPrice
FROM 
   tblAuctions AS TA 
   INNER JOIN 
      (Query1 AS Q1 
       INNER JOIN 
       tblBids AS TB 
       ON Q1.WinBid = TB.BidID)
    ON TA.AuctionID = Q1.AuctionID;

And this gets you the completed auctions with no winner.

SELECT TA.AuctionID, TA.Seller, TA.iid
FROM 
   tblAuctions AS TA 
   LEFT JOIN 
   Query1 AS Q1
   ON TA.AuctionID = Q1.AuctionID
WHERE TA.Endtime < Now()
  AND Q1.AuctionID IS NULL;

[johnseito]

you need to uniquely identify the winning Bid record itself

how, the winning bid record is a query that I created but it wasn't the correct result since it didn't take the highest of the group after I join it to the bid table
in an attempt to get more columns out of it.

your first query, this section:

 
     SELECT MAX(TB2.BidPrice)
            WHERE TB2.AuctionID = TA.AuctionID
                                              AND TB2.BidTIME <= TA.endTIME
                             AND TB2.BidPrice >= TA.minBiD)

I seem to always get an error message with this.


I am still check your other codes. : -)

[Dal Jeanis]

Ah - both the subselects are missing their FROM clauses.

FROM tblBids AS TB2

FROM tblBids AS TB3

[johnseito]

for your first code, I did some editing because I noticed your subselect is missing the FROM clauses and it only has one table instead of two.

SELECT tb.auctionID, min(tb.bidid) as winBID
FROM bid as tb  INNER JOIN auction as ta ON tb.bidid = ta.auctionid
WHERE ta.endtime < NOW() and tb.bidprice IN 


(
            SELECT MAX(TB2.BidPrice)
            FROM bid as tb2 INNER JOIN auction as ta ON tb2.bidid = ta.auctionid
            WHERE (TB2.AuctionID = TA.AuctionID) AND (TB2.BidTIME <= TA.endTIME) AND (TB2.BidPrice >= TA.minBiD)
)


GROUP BY tb.auctionid.

FROM bid as tb2 INNER JOIN auction as ta ON tb2.bidid = ta.auctionid

this is the part that I changed and I also added parenthesis.

After that changed, I got an error message, below:

[johnseito]

I noticed I have that error message bc
there was a period behind this code:

GROUP BY tb.auctionid.

I took the period away, and changed the code a bit:

SELECT tb.bidid, min(tb.bidid) as winBID
FROM bid as tb  INNER JOIN auction as ta ON tb.bidid = ta.auctionid


WHERE ta.endtime < NOW() and tb.bidprice IN (
            
            SELECT MAX(TB2.BidPrice)
            FROM bid as tb2 INNER JOIN auction as ta ON tb2.bidid = ta.auctionid
            WHERE TB2.bidid = TA.AuctionID AND TB2.BidTIME <= TA.endTIME AND TB2.BidPrice >= TA.minBiD
            )


GROUP BY tb.bidid

and now I got a result but right now I am trying see what this result is.

[Dal Jeanis]

SELECT tb.bidid, First(tb.bidprice) as winBID, min(tb.bidtime) as WinBidTIME
FROM 
   bid as tb  
   INNER JOIN 
   auction as ta 
   ON tb.bidid = ta.auctionid
WHERE ta.endtime < NOW() 
AND tb.bidprice IN (
            
            SELECT MAX(TB2.BidPrice)
            FROM 
               bid as tb2 
               INNER JOIN 
               auction as ta 
               ON tb2.bidid = ta.auctionid
            WHERE TB2.bidid = TA.AuctionID 
              AND TB2.BidTIME <= TA.endTIME 
              AND TB2.BidPrice >= TA.minBiD
            )
GROUP BY tb.bidid;

I still think you need a unique key for each bid record, and that calling the FK to auction (auctionID) the BidID is confusing.

[johnseito]

I still think you need a unique key for each bid record

You meant on the bid table right, I think you are right and that would be a good idea. The unique key in this case is a composite key of bidder,seller,iid, starttime, and bidtime.

Also I noticed your result with this query is not exactly what I was looking for.
Your query finds the highest bid price and then in your outer query join and output the bidID for this highest bidPrice.

SELECT tb.bidid, min(tb.bidid) as winBID
FROM bid as tb  INNER JOIN auction as ta ON tb.bidid = ta.auctionid


WHERE ta.endtime < NOW() and tb.bidprice IN (
            
            SELECT MAX(TB2.BidPrice)
            FROM bid as tb2 INNER JOIN auction as ta ON tb2.bidid = ta.auctionid
            WHERE TB2.bidid = TA.AuctionID AND TB2.BidTIME <= TA.endTIME AND TB2.BidPrice >= TA.minBiD
            )


GROUP BY tb.bidid

This is the correct result I was looking for (below result), is the correct result, however it doesn't have all the fields I was looking for, so I join
the result to the bid table, itemType table and user table and the result end up not being correct after the join.



This is the code that creates the output result on top.

SELECT bid.bidID, bid.seller, bid.iid, bid.starttime, max(bidprice) AS highestBID
FROM bid INNER JOIN auction ON bid.bidID = auction.auctionID
WHERE (bidprice > minbid) AND (bidtime<endtime) AND (now()>endtime)
GROUP BY bid.bidID, bid.seller, bid.iid, bid.starttime;

but it doesn't have the fields that I am looking for with the result, so I joined the bid table, user table, and item table to
get the fields I wanted, the after result is not accurate mainly because the result from my first query (bidID) is then join to bid table
(bidID - not unique #) so is pulling more than one of the records when is suppose to be pulling one, the one of the correct result.

Example: "a 5 and 9 from the result will pull all the 5 and 9 from the bid table" therefore breaching the GROUP BY clause from the correct result that find the max of the bids.

[johnseito]

Your query finds the highest bid price and then in your outer query join and output the bidID for this highest bidPrice.

In the bid table, there are many auctions, and you could tell each individual auction by looking at the unique, foreign key
which is a composite key with seller, iid, and starttime or bidID (you asked me to changed to), that is related to the auction's primary key auctionID.

Each unique composite foreign key (seller, iid, starttime) represent each auction. Your query result makes that as one whole auction, when
there are individually different auction and there could be different bidder to the same auction.

Here is the bid table.



The highlight in yellow are different bidder for that auction, that is why that auction appear twice.
Each auction is uniquely Identified by seller, iid, and starttime.

Should I make adjustment to the bid table so to make life easier and less confusion, with primary and foreign key as a single field instead of multiple fields.
I am not sure exactly how to do that, but I think bidID should be a primary key so is easier to read it, and bidauctionID should be a foreign key that ties to the auctionID primary key in the auction table.

Right now, the primary key for the bid table is a composite key of bidder, seller, iid, starttime and bidtime. The foreign key is just bidID.

Also do you know how I could get the highest winning bid for all the auction and then join them to other table so
I could have it show other fields without ruining the correct result?

[Dal Jeanis]

I think bidID should be a primary key so is easier to read it, and bidauctionID should be a foreign key that ties to the auctionID primary key in the auction table.

Exactly right.

Also do you know how I could get the highest winning bid for all the auction and then join them to other table so I could have it show other fields without ruining the correct result?

I posted my suggestion at post #15 on the other thread - https://www.accessforums.net/access/...snt-38703.html

[johnseito]

Exactly right.

Ok, so I updated the database to reflect this now.

I posted my suggestion at post #15 on the other thread - https://www.accessforums.net/access/s...snt-38703.html

I will try this out, but not that familiar with VBA/procedure of ACCESS.

After thinking of it for a bit, I figured it out and solved it with the SQL query.

here is the code:

SELECT bid.bidID, winningBID.bidauctionID, bid.bidder, user.uname as buyerNAME, winningBID.seller, user_1.uname as sellerName, 
winningBID.iid, item_type.ITEMname, winningBID.starttime, bid.bidtime, winningBID.highestBID


FROM 
           user as user_1 INNER JOIN
                    (user INNER JOIN 
                             (  Item_type INNER JOIN 
                                  (           
                                                (
                                                SELECT bid.bidauctionID, bid.seller, bid.iid, bid.starttime, max(bidprice) AS highestBID
                                                FROM bid INNER JOIN auction ON bid.bidauctionID = auction.auctionID
                                                WHERE (bidprice > minbid) AND (bidtime<endtime) AND (now()>endtime)
                                                GROUP BY bid.bidauctionID, bid.seller, bid.iid, bid.starttime
                                                )  as winningBID 
                                               INNER JOIN bid ON bid.bidauctionID = winningBID.bidauctionID AND bid.BIDprice = winningBID.highestBID
                                  )ON item_type.iid = winningbid.iid
                    ) ON user.uid=bid.bidder
            ) ON user_1.uid=winningBID.seller

all I did was do this join (below) instead of just joinning the auctionID to bidAuctionID, I also join the bidprice to the maxbidprice of the result.

INNER JOIN bid ON bid.bidauctionID = winningBID.bidauctionID AND bid.BIDprice = winningBID.highestBID

[Dal Jeanis]

Yep. That will work, as long as your system elsewhere is making sure that there can only be one bid of any given price in an auction.

Myself, I'd add a status flag to the bid record, and mark invalids, dups, losing and winning bids by status, but that's just personal preference, and your way will work.