Click here to Skip to main content
15,902,114 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have 3 SQL tables in one database:

T1: Users, with columns: ID | Username | FirstName | LastName
ID column is Primary Key
T2: Purchases, with column: IdBid | ProductCode | PurchaserID | BidPrice | BidDate
IdBid column is Primary Key
ProductCode is Foreign Key to Table 3 to column IdProduct
PurchaserID is Foreign Key to Table 1 to column ID
T3: Products, with column: IdProduct | ProductName | ProductDescription | OpeningPrice
IdProduct column is Primary Key

Tables T1 and T3 don't have any relation.


I have auction and in T2 Purchases I can have many offers for one product and each offer is new record in T2 Purchases. In C# I have to create CollectionList (inheriting ObservableCollection) which should have list of last record for each product and it should have some column from all three above mentioned tables, as follows:

T3 ProductCode | T3 ProductName | T3 ProductDescription | T3 OpeningPrice | T2 PurchaserID | T1 PurchaserName | T2 BidPrice |



How should SQL query look like to get this?

Thank you in advance

What I have tried:

Many different SQL query options I can get the last record of each Product but only to show two columns, but I couldn't get all columns which I need.
Posted
Updated 23-Jun-19 1:10am
v2

1 solution

You need to use a JOIN, combined with a subquery using a GROUP BY.

The GROUP BY will aggregate records together by product, and you can select the ProductCode and the MAX(BidDate) for each record:
SQL
SELECT ProductCode, MAX(BidDate) AS LastBidDate 
FROM Purchases 
GROUP BY ProductCode
You can then use that to retrieve the rest of the bid row:
SQL
SELECT * FROM Purchases a
JOIN (SELECT ProductCode, MAX(BidDate) AS LastBidDate 
      FROM Purchases 
      GROUP BY ProductCode ) g
ON a.ProductCode = g.ProductCode AND a.BidDate = g.LastBidDate
Then it's a trivial JOIN to add in the other info you need.
 
Share this answer
 
Comments
Sinisa Janjetovic 23-Jun-19 7:09am    
Thank you, I'll test it
OriginalGriff 23-Jun-19 7:20am    
You're welcome!
Maciej Los 23-Jun-19 7:11am    
5ed!
Herman<T>.Instance 24-Jun-19 6:52am    
Ojjj Griff! Use ROW_NUMBER function for such queries. It is designed for such db questions.

SELECT ProductCode, BidPrice, BidDate
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY ProductCode, BidDate ORDER BY ProductCode, BidDate DESC) as Rowno, ProductCode, BidPrice, BidDate
FROM Purchases
) as x
WHERE Rowno = 1
OriginalGriff 24-Jun-19 7:11am    
That'll work as well.
I just figured that for a beginner a GROUP BY and JOIN was more obvious as to what was going on.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900