April 14, 2010 at 9:14 am
I have a Table with the below structure.
ID SurveyDate FY
100 8/25/2009 2009
200 8/26/2009 2009
300 8/29/2009 2009
400 9/1/2010 2010
500 9/30/2010 2010
i want ID for each Year MaxDate. How to get that easily? I need ID only Because this ID i want to use as part of the Subquery
April 14, 2010 at 9:51 am
Something like this?
DECLARE @tbl TABLE (ID INT, SurveyDate DATETIME, FY INT )
INSERT INTO @tbl
SELECT 100 ,'8/25/2009', 2009 UNION ALL
SELECT 200 ,'8/26/2009', 2009 UNION ALL
SELECT 300 ,'8/29/2009', 2009 UNION ALL
SELECT 400 ,'9/1/2010', 2010 UNION ALL
SELECT 500 ,'9/30/2010', 2010
;WITH cte AS
(
SELECT
id,
ROW_NUMBER() OVER(PARTITION BY FY ORDER BY SurveyDate DESC ) ROW
FROM @tbl
)
SELECT id
FROM cte
WHERE ROW = 1
April 14, 2010 at 9:51 am
You are looking to do something like this:
create table #TestTab (
ID int,
SurveyDate datetime,
FY int);
insert into #TestTab
select 100, '20090825', 2009 union all
select 200, '20090626', 2009 union all
select 300, '20090829', 2009 union all
select 400, '20100901', 2010 union all
select 500, '20100930', 2010;
with MaxDatesCTE as (
select
row_number() over (partition by FY order by SurveyDate desc) as RowNum,
ID,
SurveyDate,
FY
from
#TestTab
)
select
ID, -- This is the value you want
SurveyDate -- To show you that it is the max date
from
MaxDatesCTE
where
RowNum = 1;
April 14, 2010 at 9:52 am
Well Lutz, you beat me to it, by that much ''.
April 14, 2010 at 10:12 am
Identical solutions, posted at the very same time. Must be the best solution then... 😀
Side note: '' = 44sec. That's the closest I've seen so far for posting identical solutions...
April 14, 2010 at 11:52 am
Thank you guyz.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply