CODEWITHSUNDEEP
sqlsql-server-2008-r2
Richard Ev
Richard Ev

Reputation: 54117

Getting most recent distinct records

Considering the following table:

User  CreatedDateTime   Quantity
----- ----------------- --------
Jim   2012-09-19 01:00  1
Jim   2012-09-19 02:00  5
Jim   2012-09-19 03:00  2
Bob   2012-09-19 02:00  2
Bob   2012-09-19 03:00  9
Bob   2012-09-19 05:00  1

What query would return the most recent rows (as defined by CreatedDateTime) for each User, so that we could determine the associated Quantity.

i.e. the following records

User  CreatedDateTime   Quantity
----- ----------------- --------
Jim   2012-09-19 03:00  2
Bob   2012-09-19 05:00  1

We thought that we could simply Group By User and CreatedDateTime and add a Having MessageCreationDateTime = MAX(.MessageCreationDateTime. Of course this does not work because Quantity is not available following the Group By.

Upvotes: 2

Views: 787

Answers (5)

John Woo
John Woo

Reputation: 263723

Since you are using SQL Server, you can use Window Function on this.

SELECT [User], CreatedDateTime, Quantity
FROM
    (
        SELECT [User], CreatedDateTime, Quantity,
                ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY CreatedDateTime DESC) as RowNum
        FROM tableName
    ) a
WHERE  a.RowNum = 1

SQLFiddle Demo

Upvotes: 4

user1675644
user1675644

Reputation: 21

select * from <table_name> where CreatedDateTime in (select max(CreatedDateTime) from <table_name> group by user) group by user;

Upvotes: 0

Taryn
Taryn

Reputation: 247690

If you do not have the ability to use windowing functions, then you can use a sub-query:

select t1.[user], t2.mxdate, t1.quantity
from yourtable t1
inner join
(
    select [user], max(CreatedDateTime) mxdate
    from yourtable
    group by [user]
) t2
    on t1.[user]= t2.[user]
    and t1.CreatedDateTime = t2.mxdate

see SQL Fiddle with Demo

Upvotes: 1

anon
anon

Reputation: 

;WITH x AS
(
  SELECT [User], CreatedDateTime, Quantity, 
   rn = ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY CreatedDateTime DESC)
  FROM dbo.table_name
)
SELECT [User], CreatedDateTime, Quantity
FROM x WHERE rn = 1;

Upvotes: 4

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

SELECT DISTINCT 
User, 
CreatedDateTime, 
Quantity
FROM 
YourTable
WHERE 
CreatedDateTime = 
(SELECT MAX(CreatedDateTime) FROM YourTable t WHERE t.User = YourTable.User)

Upvotes: 0

Related Questions