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

OVER and PARTITION BY. Please explain Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 4:32 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:44 PM
Points: 8, Visits: 24
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
Post #1450826
Posted Wednesday, May 8, 2013 5:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1450836
Posted Wednesday, May 8, 2013 5:24 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:44 PM
Points: 8, Visits: 24
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
Post #1450840
Posted Wednesday, May 8, 2013 5:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1450841
Posted Wednesday, May 8, 2013 5:42 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:44 PM
Points: 8, Visits: 24
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.

StudentID QualsFound UnitsFound
TECNQ13137 1 1
TECNQ11105 66 66
ATCNQ08015 68 68
TECNQ12016 46 46
TECNQ12354 128 128
TECNQ13090 1 1
TECNQ12265 45 45
TECNQ11201 12 12

How can I do the above but with DISTINCT in each COUNT() function?

Thanks


http://www.tecnq.com.au
Post #1450844
Posted Wednesday, May 8, 2013 5:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1450845
Posted Wednesday, May 8, 2013 5:49 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:44 PM
Points: 8, Visits: 24
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
Post #1450847
Posted Wednesday, May 8, 2013 6:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 6,237, Visits: 7,392
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1450848
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse