-
May 6th, 2005, 02:11 PM
#1
Select unique latest record from sql database
I have a log table like this:
username productID purchasetime
---------------------------------------------------
user1 12 01/02/1999
user1 12 03/05/2000
user2 23 02/03/2000
user2 31 02/04/2000
user2 23 02/04/2000
....
I want to select users that purchased product later than a predefined time, say 01/01/1998, but if a user purchase a same product at different dates, then only the latest one will be selected. How to write the query? Thanks a lot.
-
May 7th, 2005, 01:04 AM
#2
Re: Select unique latest record from sql database
Code:
Select UserName from table where purchasetime in (select max(purchasetime) from table grpup by username)
Madhivanan
Failing to plan is Planning to fail
-
March 9th, 2011, 08:27 AM
#3
Re: Select unique latest record from sql database
Thanks..
But a new problem.
I have a separate table to keep Students' Address. It saves the DateGiven of that address. How can I Read the Latest Address of each student. I tried the following but error occours. Please help me.
SELECT Std_ID, Line1, Line2, City
FROM AddressBook
WHERE Std_ID IN (SELECT Std_ID, MAX(DateGiven) FROM AddressBook GROUP BY Std_ID)
=======
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
=======
-
March 9th, 2011, 09:26 AM
#4
Re: Select unique latest record from sql database
Try:
Code:
SELECT Std_ID, Line1, Line2, City
FROM AddressBook AB1
WHERE AB1.DateGiven
IN (SELECT MAX(AB2.DateGiven)
FROM AddressBook AB2
WHERE AB2.Std_ID = AB1.Std_ID)
-
March 10th, 2011, 03:31 AM
#5
Re: Select unique latest record from sql database
If using SQL Server 2005 or higher, then the ROW_Number() can help you to avoid having to make the subqueries and MAX.
Just make something like
Row_Number() OVER (Partition By username Order By purchasetime DESC) AS RowNumber
and then select where RowNumber is 1.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|