May 8, 2013 at 4:32 pm
Hi.
We have a function that is meant to return currently enrolled programs for students doing certain courses.
In the functions we have this ...
row_number() OVER (PARTITION BY r.StudentID
ORDER BY
ISNULL(r.StudProgEnrolStatusID, 3),
r.EnrolmentDate,
r.ProgramEnrolmentID DESC)
'r' is the alias to our table which holds what students are enrolled in certain programs.
The StudProgEnrolStatusID = 3 means that they are a specific type of student studying a specific type of programs / courses if you will.
What is happening here please? What if OVER(PARTITION... meant to do?
I am trying to re-write this so that it has the same result without the convoluted functions above, so that other SQL developers here can understand the code better.
Thanks
http://www.tecnq.com.au
May 8, 2013 at 5:07 pm
In case you're simply having google-fu failure, here's the link explaining the code:
http://msdn.microsoft.com/en-us/library/ms186734.aspx
MSDN article on ROW_NUMBER().
OVER is a keyword to tell it you're about to tell it your intentions, that's all.
PARTITION BY is when it starts to renumber. Think of a group by. It'll give you a different row_number() for each row with the same value, then restart at 1 for the next value. ORDER BY simply tells it what order to apply the 1,2,3,4,5... into.
There is no alternative to using that windows funtion. It's rather powerful and very effective for the cases where it's needed.
The question is what's the rest of the query that it's used in?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 8, 2013 at 5:24 pm
Thanks Craig. I found that link too (yes I can use Google), but I didn't understand it. Your reply made more sense actually. Thanks
http://www.tecnq.com.au
May 8, 2013 at 5:27 pm
TecNQ (5/8/2013)
Thanks Craig. I found that link too (yes I can use Google), but I didn't understand it. Your reply made more sense actually. Thanks
No worries, usually it's a case of keyword choices sometimes being painful and msdn hiding a few pages deep. Heck, I usually ask for help on some things simply cause I can't remember them, so that wasn't intended to be a slight.
If you present the rest of the query and logic behind why it's looking to get those row numbers we can probably pull it apart and look for an alternative if that's part of your requirement.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 8, 2013 at 5:42 pm
Hi Craig.
It's ok. I now understand the code, and how it relates to the query overall.
But I have a question... should be simple to answer.
I have the following code ...
SELECT DISTINCT su.StudentID,
COUNT(su.QualificationCode) OVER (PARTITION BY su.StudentID) as QualsFound,
COUNT(su.UnitCode) OVER (PARTITION BY su.StudentID) as UnitsFound
FROM tblStudentUnitRegister su
WHERE su.QualificationCode IS NOT NULL
AND su.UnitCode IS NOT NULL
What I am trying to do is report by each student ID, the number of Qualifications and Units.
But as you may have already seen, it's reporting the same COUNT() for each.
i.e.
StudentIDQualsFoundUnitsFound
TECNQ1313711
TECNQ111056666
ATCNQ080156868
TECNQ120164646
TECNQ12354128128
TECNQ1309011
TECNQ122654545
TECNQ112011212
How can I do the above but with DISTINCT in each COUNT() function?
Thanks
http://www.tecnq.com.au
May 8, 2013 at 5:45 pm
TecNQ (5/8/2013)
What I am trying to do is report by each student ID, the number of Qualifications and Units.But as you may have already seen, it's reporting the same COUNT() for each.
... How can I do the above but with DISTINCT in each COUNT() function?
Thanks
Easier than it looks:
SELECT DISTINCT
su.StudentID,
COUNT(DISTINCT su.QualificationCode) OVER (PARTITION BY su.StudentID) as QualsFound,
COUNT(DISTINCT su.UnitCode) OVER (PARTITION BY su.StudentID) as UnitsFound
FROM
tblStudentUnitRegister su
WHERE
su.QualificationCode IS NOT NULL
AND su.UnitCode IS NOT NULL
However, that is overkill, as you're simply using a single table. I would recommend swapping that to this:
SELECT
su.StudentID,
COUNT( DISTINCT su.QualificationCode) AS QualsFound,
COUNT( DISTINCT su.UnitCode) AS UnitsFound
FROM
tblStudentUnitRegister su
WHERE
su.QualificationCode IS NOT NULL
AND su.UnitCode IS NOT NULL
GROUP BY
su.StudentID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 8, 2013 at 5:49 pm
Thanks again.
The last code needed a GROUP BY to work, but it works!
The first one didn't. It came back saying "Incorrect syntax near DISTINCT".
I assume that's the COUNT(DISTINCT...)?
Cheers
http://www.tecnq.com.au
May 8, 2013 at 6:07 pm
TecNQ (5/8/2013)
Thanks again.The last code needed a GROUP BY to work, but it works!
The first one didn't. It came back saying "Incorrect syntax near DISTINCT".
I assume that's the COUNT(DISTINCT...)?
Cheers
Yeah, sorry, you got here before I could finish my edit on the GROUP BY, realized I'd missed it when I copy/pasted. 😀
Count OVER should allow for DISTINCT, but you're right, it's most likely in there. I rarely use the windows aggregations functions unless I'm doing something incredibly odd, like including multiple aggregations in different patterns off the same dataset and don't want to repull the data.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply