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
 General SQL Server Forums
 New to SQL Server Programming
 most recent date		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Posting Yak Master

151 Posts
Posted - 03/08/2013 :  10:45:56  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 03/08/2013 :  10:48:26  Show Profile  Reply with Quote 

SELECT id,date
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RN,*
FROM Table
)t
WHERE RN=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rjhe22
Posting Yak Master

151 Posts
Posted - 03/08/2013 :  10:51:23  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 03/08/2013 :  10:52:58  Show Profile  Reply with Quote
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
Posting Yak Master

151 Posts
Posted - 03/08/2013 :  10:56:25  Show Profile  Reply with Quote
ok thanks will try that and see
Go to Top of Page

rjhe22
Posting Yak Master

151 Posts
Posted - 03/08/2013 :  11:05:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3821 Posts
Posted - 03/08/2013 :  12:18:50  Show Profile  Reply with Quote
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

India
47023 Posts
Posted - 03/08/2013 :  13:07:17  Show Profile  Reply with Quote
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
  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.06 seconds. Powered By: Snitz Forums 2000