First let me say this: I cannot use the Distinct keyword. There are column types in the returned data that it cannot compare.
Here is my query:
SELECT
tblTemp.maxBid, tblAbsentee.abs_bid_price,
tblAbsentee.abs_date, tblSale.sale_name, tblSale.sale_date,
tblItem.*,
tblTemp.bid_count
FROM
tblAbsentee
INNER JOIN
tblItem ON tblAbsentee.abs_item_id = tblItem.item_id AND tblAbsentee.abs_sale_id = tblItem.item_sale_id
INNER JOIN
tblBidder ON tblAbsentee.abs_bidder_id = tblBidder.bidder_number AND tblAbsentee.abs_sale_id = tblBidder.bidder_sale_id
LEFT JOIN
tblSale ON tblAbsentee.abs_sale_id = tblSale.sale_id
INNER JOIN
(SELECT
COUNT(tblAbsentee.abs_id) As bid_count,
CASE
WHEN NOT (MAX(tblAbsentee.abs_bid_price) IS NULL) THEN MAX(tblAbsentee.abs_bid_price) ELSE 0
END As maxBid,
tblItem.item_id
FROM
tblAbsentee
INNER JOIN
tblItem ON tblAbsentee.abs_item_id = tblItem.item_id AND tblAbsentee.abs_sale_id = tblItem.item_sale_id
INNER JOIN
tblBidder ON tblAbsentee.abs_bidder_id = tblBidder.bidder_number AND tblAbsentee.abs_sale_id = tblBidder.bidder_sale_id
LEFT JOIN
tblSale ON tblAbsentee.abs_sale_id = tblSale.sale_id
WHERE
DATEADD(day, 7, tblSale.sale_date) >= GETDATE()
GROUP BY
tblItem.item_id) As tblTemp On tblItem.item_id = tblTemp.item_id AND tblAbsentee.abs_bid_price = tblTemp.maxBid
WHERE
DATEADD(day, 7, tblSale.sale_date) >= GETDATE()
AND tblBidder.bidder_mail_id = 13096
ORDER BY
tblItem.item_lot ASC
So long as the bidder only leaves one bid, this works fine. But as soon as the bidder leaves another bid I get the error
System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Due to using a data table for the results.
Here's some short sample data:
+--------+-------------+--------------------+-------+
|Max Bid |Abs_bid_price|abs_date |item_id|
+--------+-------------+--------------------+-------+
|600.0000|600.0000 |1/15/2014 2:40:16 PM|135827 |
+--------+-------------+--------------------+-------+
|600.0000|600.0000 |1/15/2014 2:40:16 PM|135827 |
+--------+-------------+--------------------+-------+
|600.0000|600.0000 |1/15/2014 2:40:16 PM|135827 |
+--------+-------------+--------------------+-------+
|600.0000|600.0000 |1/15/2014 2:40:16 PM|135827 |
+--------+-------------+--------------------+-------+
|600.0000|600.0000 |1/15/2014 2:40:16 PM|135827 |
+--------+-------------+--------------------+-------+
|600.0000|600.0000 |1/15/2014 2:40:16 PM|135827 |
+--------+-------------+--------------------+-------+
|20.0000 |20.0000 |1/15/2014 2:42:16 PM|123562 |
+--------+-------------+--------------------+-------+
I'm not going to bother putting in all of the columns. Just trust me when I say that they all contain the same data. In most cases, the abs_date column would NOT be the exact same. This was just a fluke. In either case, I would like to just group it by item_id and sort it by the abs_date field asc when grouping so that the first max bid left will be the one shown. If this was MySQL it'd be easy. I just don't know enough about SQL Server to figure this out, unfortunately, and cannot find a similar issue. Any help is much appreciated.
EDIT
It may not have been clear in my question, but I could validly get multiple rows returned, I just want to only get one of each item ID.
You can use a Common Table Expression. This will return the first occuring item_id.
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY abs_date) Rank
FROM tblAbsentee
...
)
SELECT *
FROM CTE
WHERE Rank = 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments