I'm trying to select the distinct products, grouped by all columns except the ID, and the cheapest bid for that type of product. Sometimes, there may be no bids for that product, so I'll need to left join to account for those.
I'll add the desired output below and also a sort-of pseudo query to help explain.
If it helps, you can imagine a product search page. When the user searches, it groups all the same products together. These products can be sold by many different sellers but we want to only show it once within the results, along with the cheapest price for that product at the time of the search.
Important: An 'Active' bid can be cancelled, which overrides the status for that row. But, due to how this system works, if the bid is accepted (filled), it creates a new bid row for that ProductID. So, I need the cheapest bid that is still active and doesn't have a 'filled' bid after, based on the TimeOfBid field.
Statuses
- Cancelled - Updates the BidID from Active to Cancelled (overriding)
- Active - An active bid
- Filled - This is a new row. If it's the latest row, it means there is no longer any active bids.
Data I want returned
SELECT count(Name), Size, Weight, Color, BidID, Status, min(Price)
-- WHERE the bid is still active (has 'Active' status and no 'Filled' bid with later time) --
-- AND is the cheapest price
-- group by Size, Weight, Color, BidID, Status
Will also require WHERE clauses, which are built based on filters used by end user. Examples:
WHERE Status = 'Active' and Weight = '1kg'
WHERE Status = 'Active'
WHERE Size = '1m' and Weight in ('1kg', '2kg')
Products Table
| ProductID (PK) | Name | Size | Weight | Color |
| -------- | -------- | -----| ----- | ----- |
| 1 | Black Table | 1m | 2kg | Black |
| 2 | Purple Table| 1m | 3kg | Purple |
| 3 | Small Table | 1m | 3kg | Black |
| 4 | Small Table | 1m | 3kg | Black |
| 5 | Black Table | 1m | 2kg | Black |
| 6 | Purple Table| 1m | 3kg | Purple |
| 7 | Small Table | 1m | 3kg | Black |
| 8 | Small Table | 1m | 3kg | Black |
| 9 | Big Table | 2m | 4kg | Gray |
Bids Table
| BidID (PK) | ProductID | Status | Price | TimeOfBid |
| -------- | ------- | -------- | ----- | ------------------- |
| 1 | 1 | Filled | 123.5 | 2022-09-20 02:51:41 | <= ID 1 now has no active bid
| 2 | 2 | Cancelled | 325.99 | 2022-09-20 02:50:28 |
| 3 | 1 | Active | 85.99 | 2022-09-20 01:16:24 |
| 4 | 3 | Cancelled | 315.99 | 2022-09-20 01:15:58 |
| 5 | 4 | Active | 113.5 | 2022-09-20 01:15:39 | <= Cheapest for ID 4
| 6 | 3 | Cancelled | 305.99 | 2022-09-20 01:15:12 |
| 7 | 1 | Active | 82.99 | 2022-09-20 01:14:46 |
| 8 | 2 | Active | 345.99 | 2022-09-20 01:13:33 | <= Cheapest for ID 2
DESIRED OUTPUT EXAMPLE
| Count | Name | Size | Weight | Color | BidID | CheapestPrice |
| -------- | -------- | -----| ----- | ----- | ----- | ----- |
| 38 | Black Table | 1m | 2kg | Black | 1 | 123.5
| 21 | Purple Table| 1m | 3kg | Purple | 2 | 89.95
| 13 | Small Table | 1m | 3kg | Black | 3 | 65.94
| 6 | Big Table | 2m | 4kg | Gray | NULL | NULL
Someone helped me get close yesterday (I failed to give the full picture) with the below query. It just doesn't take into account that the bids are overridden with newer Bid rows with Status set to 'Filled'. It also doesn't return the count() of grouped products.
WITH CTE AS
(SELECT P.PRODUCTID PPID,NAME,SIZE,WEIGHT,COLOR
,B.BIDID,B.PRODUCTID AS BPID,STATUS,PRICE
,ROW_NUMBER() OVER (PARTITION BY NAME,SIZE,WEIGHT,COLOR ORDER BY PRICE) RN
FROM PRDUCTS P
JOIN BIDS B ON B.PRODUCTID = P.PRODUCTID
)
SELECT PPID,NAME,SIZE,WEIGHT,COLOR,PRICE FROM CTE
WHERE RN = 1
UNION ALL
SELECT DISTINCT PRODUCTID,NAME,SIZE,WEIGHT,COLOR,NULL
FROM PRDUCTS P
WHERE NOT EXISTS
(SELECT 1 FROM CTE WHERE CTE.NAME = P.NAME AND
CTE.WEIGHT = P.WEIGHT AND
CTE.COLOR = P.COLOR);