SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Show Unique rows depending on date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

murd
Starting Member

24 Posts

Posted - 02/11/2014 :  14:50:08  Show Profile  Reply with Quote
username,   date,    value   status   created_date
-----------------------------------------------------
brad,     1/2/2010,  desc1      H    1/2/2010
brad,     1/2/2010,  desc1      S    1/3/2010
brad,     1/2/2010,  desc1      C    1/12/2010
brad,     1/2/2010,  desc2      H    1/2/2010
brad,     1/2/2010,  desc2      S    1/3/2010
brad,     1/2/2010,  desc2      x    1/12/2010


result


username,   date,    value   status   created_date
-----------------------------------------------------
brad,     1/2/2010,  desc1      C    1/12/2010
brad,     1/2/2010,  desc2      x    1/12/2010


I would like to capture records for each distinct value and user depending on the most recent created date

Edited by - murd on 02/11/2014 14:58:55

shilpash
Posting Yak Master

103 Posts

Posted - 02/11/2014 :  15:26:23  Show Profile  Reply with Quote
WITH maxdate
AS ( SELECT value ,
MAX(createdate) createddate
FROM TABLENAME
GROUP BY value
)
SELECT a.username ,
a.date ,
a.value ,
a.status ,
a.createdate
FROM TABLENAME a
JOIN maxdate b ON a.value = b.value
AND a.createdate = b.createddate
Go to Top of Page

murd
Starting Member

24 Posts

Posted - 02/12/2014 :  00:18:07  Show Profile  Reply with Quote
quote:
Originally posted by shilpash

WITH maxdate
AS ( SELECT value ,
MAX(createdate) createddate
FROM TABLENAME
GROUP BY value
)
SELECT a.username ,
a.date ,
a.value ,
a.status ,
a.createdate
FROM TABLENAME a
JOIN maxdate b ON a.value = b.value
AND a.createdate = b.createddate




This will work...Bravo!!!

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000