Maximum record for each Resourceid

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

Maximum record for each Resourceid

Author

Topic

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-12 : 08:56:07

Hi All,
I was trying to find the maximum record for each Resourceid

eg:- if given table is as below

100 ,'2010-08-19', '2011-08-26' UNION ALL
100 ,'2010-04-19', '2012-05-06' UNION ALL
100 ,'2010-12-09', '2012-01-01' UNION ALL
101 ,'2009-08-14', '2011-08-25'

we should get
100 ,'2010-12-09', '2012-01-01' UNION ALL
101 ,'2009-08-14', '2011-08-25'

I tried it by Row_number() over (partition by RID Order by RID)
but how to pick the maximum record for each resourceID

DECLARE @AA table
(
RID int,
REffdt datetime,
RExpdt datetime
)
INSERT INTO @AA
SELECT 100 ,'2010-08-19', '2011-08-26' UNION ALL
SELECT 100 ,'2010-04-19', '2012-05-06' UNION ALL
SELECT 100 ,'2010-12-09', '2012-01-01' UNION ALL
SELECT 101 ,'2009-08-14', '2011-08-25'

SELECT * FROM @AA

I tried below :-
SELECT row_number() over (partition by RID order by Rid),RID,REffdt,RExpdt
FROM @AA

Thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-12 : 09:03:33

select *
from
(
SELECT seq = row_number() over (partition by RID order by REffdt desc),RID,REffdt,RExpdt
FROM @AA
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-12 : 09:16:13

Hi Nigelrivett,
Your query is perfect when REffdate is in increasing order

when one more data gets added for RID=100
RID REffdt RExpdt
100 2010-10-09 2011-01-01

Then query would not pick right data.
I want the maximum entered data for each resource

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-12 : 09:18:47

i.e.latest entered/maximum

help will be highly appreciated

RID REffdt RExpdt
100 2010-10-09 2011-01-01
101 2009-08-14 2011-08-25

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-12 : 09:38:48

Apply double function

select * from
(
	select *,seq1=row_number() over (partition by RID order by seq desc) 
	from
	(
	SELECT seq = row_number() over (partition by RID order by (select 1)),RID,REffdt,RExpdt
	FROM @AA
	) as a
) as t
where seq1 = 1

Madhivanan

Failing to plan is Planning to fail

Vishal_sql
Posting Yak Master

102 Posts

Posted - 2012-07-12 : 09:53:19

Thanks madhivanan and nigel for your replies.
Logic is perfect and I beleive it wont take longer when applied to large dataset.
Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources