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
 Need Help selecting with grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gmartinezlozada
Starting Member

USA
3 Posts

Posted - 03/04/2014 :  14:40:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/04/2014 :  17:16:16  Show Profile  Reply with Quote
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
Go to Top of Page

gmartinezlozada
Starting Member

USA
3 Posts

Posted - 03/05/2014 :  12:52:13  Show Profile  Reply with Quote
Thank you so much. i will try this and let you know.
Go to Top of Page

gmartinezlozada
Starting Member

USA
3 Posts

Posted - 03/07/2014 :  09:19:12  Show Profile  Reply with Quote
Works like a charm. thanks a lot.
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.05 seconds. Powered By: Snitz Forums 2000