OVER and PARTITION BY. Please explain

  • 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

  • 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?


    - Craig Farrell

    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

  • 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

  • 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.


    - Craig Farrell

    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

  • 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

  • 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


    - Craig Farrell

    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

  • 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

  • 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.


    - Craig Farrell

    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