SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OVER and PARTITION BY. Please explain


OVER and PARTITION BY. Please explain

Author
Message
TecNQ
TecNQ
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21723 Visits: 7660
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
TecNQ
TecNQ
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21723 Visits: 7660
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
TecNQ
TecNQ
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21723 Visits: 7660
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
TecNQ
TecNQ
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21723 Visits: 7660
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. :-D

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search