Need Help selecting with grouping

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

Need Help selecting with grouping

Author

Topic

gmartinezlozada
Starting Member

3 Posts

Posted - 2014-03-04 : 14:40:06

I have a table and I need three columns:
AdEnrollID (Foreign Key), SyStatchangeID (Primary KEY) and EffectiveDate (DateTime). I need the MAX(EffectiveDate) group by AdEnrollID but I also need the systatchangeID for that row.

here is my query:
Select AdEnrollID,SyStatChangeid, MAX(EffectiveDate)
from systatchange where EffectiveDate < '8/1/2013' and adenrollID=47964
Group by grouping sets ((SyStatChangeID, systatchange.AdEnrollID))

but it gives me a lot of results (I know, I group by systatchangeID and is going to include them all)

Here is the result:
AdEnr SyStat (No column name)
47964 274956 2012-08-07 12:44:36.787
47964 277286 2012-09-05 09:32:02.480
47964 278621 2012-09-13 13:45:11.840
47964 279106 2012-09-19 14:58:33.080
47964 285823 2012-12-07 15:14:01.087
47964 288130 2011-06-09 00:00:00.000
47964 291992 2011-06-09 00:00:00.000
47964 307190 2011-06-09 00:00:00.000
47964 246461 2011-06-09 14:46:22.823
47964 246462 2011-06-09 14:46:23.120
47964 263517 2012-01-25 13:31:11.490
47964 273669 2012-07-23 12:17:41.033
47964 311992 2011-06-09 00:00:00.000
I am only interested in the one in bold. How can I do this.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-04 : 17:16:16

You can use th erow number function like this:

SELECT   AdEnrollID ,
        SyStatChangeid ,
        EffectiveDate
FROM (
	SELECT  AdEnrollID ,
			SyStatChangeid ,
			EffectiveDate,
			ROW_NUMBER() OVER (PARTITION BY AdEnrrolID ORDER BY EffectiveDate DESC ) AS RN
	FROM    systatchange
	WHERE   EffectiveDate < '8/1/2013'
			AND adenrollID = 47964
) s
WHERE RN = 1;

Also take a look at this thread for couple of alternatives, with explanations. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=192905

gmartinezlozada
Starting Member

3 Posts

Posted - 2014-03-05 : 12:52:13

Thank you so much. i will try this and let you know.

gmartinezlozada
Starting Member

3 Posts

Posted - 2014-03-07 : 09:19:12

Works like a charm. thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources