Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
See more:
hello i have two table in my database and want to select last record of each user
suppose my table are Customer(UserID,Name,Contact), another table purchase (productID,productName,UserID) here in purchase table userID is foreign key so i want to retrieve the last productname and productiD of each and every Customers from database by using inner join please help me.

What I have tried:

i have tried with the subquiry but it is not work for me it will show last record of the last client
Posted
Updated 20-Oct-16 2:54am
Comments
[no name] 20-Oct-16 8:51am    
The "last record" according to what criteria? Get the top 1 record sorted descending will be the last...
AZHAR SAYYAD 20-Oct-16 8:54am    
the last record is acording to purchase id
[no name] 20-Oct-16 8:57am    
Well then there's your answer.

1 solution

Assuming that your ProductID is an IDENTITY field, so the higher the number the later the INSERT:
SQL
SELECT c.Name, p.productName FROM Customer c
JOIN (SELECT UserID, MAX(ProductID) as MaxPID FROM Purchases GROUP BY UserID) pu
ON c.UserId = pu.UserID
JOIN Purchases p
ON p.ProductID = pu.MaxPID
 
Share this answer
 
Comments
AZHAR SAYYAD 20-Oct-16 9:44am    
Thanks this work for me SELECT c.Name, p.productName FROM Customer c
JOIN (SELECT UserID, MAX(ProductID) as MaxPID FROM Purchases GROUP BY UserID) pu
ON c.UserId = pu.UserID
JOIN Purchases p
ON p.ProductID = pu.MaxPID
OriginalGriff 20-Oct-16 9:56am    
You're welcome!
Herman<T>.Instance 25-Jun-19 8:34am    
ahummmm....ROW_NUMBER?
OriginalGriff 25-Jun-19 9:26am    
Only works if you have something to ORDER BY - without that, SQL can in theory return rows in any order it desires. Since he doesn't have a timestamp column the ID is about the only thing he can assume is sequential.
Herman<T>.Instance 26-Jun-19 4:05am    
Why you think row_number only works with timestamp column? order by Id DESC is valid for Row_Number

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