Click here to Skip to main content
14,983,808 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.
   
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.
Herman<T>.Instance 24-Jun-19 7:51am
   
Both add to the learning curve
OriginalGriff 24-Jun-19 9:43am
   
Hah! This is SQL: it has a "Learning Brick Wall" rather than a curve, much like MSDOS manuals used to.

Have you ever looked at the docs for PIVOT and UNPIVOT? :laugh:
Herman<T>.Instance 24-Jun-19 19:03pm
   
have you ever read my article about dynamic pivotting?
OriginalGriff 25-Jun-19 1:34am
   
I didn't even know it existed!

And I'm halfway through my first coffee of the day (it's 06:30 here) so I'm not going to read it right now, my brain will run away and hide for the day if I try ... :laugh:

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