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 |
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-08 : 10:45:56
|
hiim trying to write a quick that pulls the most reason dates for each id i have so for instance id date1 1/01/20131 1/2/20131 1/3/20132 1/01/20132 1/02/20133 1/03/2013so 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,dateFROM(SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RN,*FROM Table)tWHERE RN=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
rjhe22
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-08 : 10:56:25
|
ok thanks will try that and see |
|
|
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 |
|
|
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 TableAINNER JOIN ( SELECT ID, MAX(Date) AS [Date] FROM TableName GROUP BY ID ) AS B ON TableA.ID = B.ID |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-08 : 13:07:17
|
i think this is what op's asking aboutSELECT t.id,t.date,t1.*FROM(SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RN,*FROM Table)tINNER JOIN OtherTable t1ON t1.column = t.relatedcolumnWHERE RN=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|