Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Row_Number over partition Help Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 5:49 AM
Points: 2, Visits: 6
Hi there i am having problems getting my last revision number out when i am trying to use inner joins along with the row_number over partition

I am using 2 tables, tbl_acyear_lookup & tbl_targets
tbl_acyear_lookup columns = (pk)- academic_year_id, academic_year
looks like this:
1, 2010/2011
2, 2011/2012
3, 2012/2013

tbl_targets columns = targetID, Academic_Year_ID,Course_Mode,UK_ENROL, INT_ENROL, Notes, Revision_Number

I have one stored proc that uses the Row_number over partition that looks like this:

Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV where (RV.RevNum=1)


Now the next store proc needs to use the above but i need to add the Academic_year from the tbl_acyear_lookup table also add filter the target_year ='year 1'

Any help Please?


Post #1519969
Posted Thursday, December 5, 2013 5:39 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:32 PM
Points: 4,237, Visits: 3,257
Hello and welcome to the forums. To make it easier for everyone to help, could you please post the following?

1. DDL to create the tables and DML to populate some sample data.
2. A description of your requirements.
3. Your expected results.

The second link in my signature describes best practices on how to post questions.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1519979
Posted Thursday, December 5, 2013 5:41 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 706, Visits: 1,264
i hope i understand it right,

you need to join the table "tbl_acyear_lookup" with the query to

Select TargetID, Academic_Year_id, Course_Mode, UK_Enrol, Int_Enrol, Notes, Revision_Number 
, ACY.academic_year
from
(SELECT ROW_NUMBER() OVER (partition by [Academic_Year_id] order by [Revision_Number] DESC) as [RevNum],TargetID, Academic_Year_id, Course_Mode, Target_Year, UK_Enrol, Int_Enrol, Notes, Revision_Number
FROM tbl_targets where course_mode=@course_mode) RV
Inner join tbl_acyear_lookup AS ACY on RV.Academic_Year_id = ACY.Academic_Year_id
where (RV.RevNum=1)

what i didnt understand at all is the following

"add filter the target_year ='year 1'"

what do you mean why saying adding the filter
Post #1519980
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse