Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
murd
Starting Member
24 Posts |
 Posted - 2014-02-11 : 14:50:08
|
| [code]username, date, value status created_date-----------------------------------------------------brad, 1/2/2010, desc1 H 1/2/2010brad, 1/2/2010, desc1 S 1/3/2010brad, 1/2/2010, desc1 C 1/12/2010brad, 1/2/2010, desc2 H 1/2/2010brad, 1/2/2010, desc2 S 1/3/2010brad, 1/2/2010, desc2 x 1/12/2010[/code]result [code]username, date, value status created_date-----------------------------------------------------brad, 1/2/2010, desc1 C 1/12/2010brad, 1/2/2010, desc2 x 1/12/2010[/code]I would like to capture records for each distinct value and user depending on the most recent created date |
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2014-02-11 : 15:26:23
|
| 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 |
 |
|
|
murd
Starting Member
24 Posts |
Posted - 2014-02-12 : 00:18:07
|
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!!! |
|
|
|
|
|
|
|
|
Show Unique rows depending on date