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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 most recent date

Author  Topic 

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-08 : 10:45:56
hi
im trying to write a quick that pulls the most reason dates for each id i have

so for instance
id date
1 1/01/2013
1 1/2/2013
1 1/3/2013
2 1/01/2013
2 1/02/2013
3 1/03/2013

so when i run my query it will pull back the 1/03/2013 for my ids

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 10:48:26
[code]
SELECT id,date
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RN,*
FROM Table
)t
WHERE RN=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-08 : 10:51:23
thanks for quick response.
if i dont want to have to always put in id= 1 or 2 etc as my table has over 5000 id will i have to use a variable or temp table to do it
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 10:52:58
you dont need to put id = 1,2 etc. it will give you recent date for each id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-08 : 10:56:25
ok thanks will try that and see
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-08 : 11:05:15
if i wanted to use that in an inner join with another table would it take much working around
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-08 : 12:18:50
quote:
Originally posted by rjhe22

if i wanted to use that in an inner join with another table would it take much working around

Something like this maybe?
SELECT 
*
FROM
TableA
INNER JOIN
(
SELECT ID, MAX(Date) AS [Date]
FROM TableName
GROUP BY ID
) AS B
ON TableA.ID = B.ID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 13:07:17
i think this is what op's asking about

SELECT t.id,t.date,t1.*
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RN,*
FROM Table
)t
INNER JOIN OtherTable t1
ON t1.column = t.relatedcolumn
WHERE RN=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -