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 by ....order by) Expand / Collapse
Author
Message
Posted Monday, November 1, 2010 5:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 23, 2011 8:57 AM
Points: 15, Visits: 46
Hi guys,

can anyone please explain to me in plain english what this update statement is doing (mainly the join select)
Many thanks

update tblEMAIL
set EMAIL_No = EMAILNUMBER
from tblEMAIL a
JOIN (SELECT EMAIL_ID,
row_number() over (partition by PersonID, Department order by EMAIL_ID) EMAILNUMBER
from tblEMAIL) b
on a.EMAIL_ID = b.EMAIL_ID

Post #1013757
Posted Monday, November 1, 2010 5:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
Well for 1, this is a 2005+ query and you posted in sql 2000 forum.

#2, you can find all you need to know in books online 2k5 about this.

The short answer is that this query is manually building a identity for each person / departement combinaisons.


I will suggest you copy the tables to a dev environement and run the query yourself. You'll see exactly what it's doing.
Post #1013764
Posted Friday, November 5, 2010 4:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:34 AM
Points: 146, Visits: 124
To get you started - the row_number() function will return a sequence of numbers starting at 1 effectively allocating a number to each EMAIL_ID in EMAIL_ID order. The sequence restarts at 1 for each (PersonID, Department) combination.

The join allows the number generated by the row_number() function to be set up as the EMAIL_NO in tblEMAIL.




--------------------------------------------------------------

“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
Post #1016434
Posted Friday, November 5, 2010 5:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 5,370, Visits: 9,008
You might want to read my article that covers this at SQL Server Ranking Functions

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1016455
Posted Friday, November 5, 2010 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
askquestions (11/1/2010)
Hi guys,

can anyone please explain to me in plain english what this update statement is doing (mainly the join select)
Many thanks

update tblEMAIL
set EMAIL_No = EMAILNUMBER
from tblEMAIL a
JOIN (SELECT EMAIL_ID,
row_number() over (partition by PersonID, Department order by EMAIL_ID) EMAILNUMBER
from tblEMAIL) b
on a.EMAIL_ID = b.EMAIL_ID



Rewritten to make it look a little more conventional (to me):

UPDATE a
SET EMAIL_No = b.EMAIL_No
FROM tblEMAIL a
JOIN (
SELECT EMAIL_ID,
EMAIL_No = ROW_NUMBER() OVER (PARTITION BY PersonID, Department ORDER BY EMAIL_ID)
FROM tblEMAIL
) b
ON a.EMAIL_ID = b.EMAIL_ID

I reckon this will set the whole EMAIL_No column to 1, like this:

DROP TABLE #Email
CREATE TABLE #Email (EMAIL_ID INT IDENTITY, EMAIL_No INT, PersonID INT, Department VARCHAR(25))
INSERT INTO #Email (EMAIL_No, PersonID, Department)
SELECT 1, 1, 'Accounts' UNION ALL
SELECT 1, 2, 'Accounts' UNION ALL
SELECT 1, 3, 'Accounts' UNION ALL
SELECT 1, 4, 'Accounts' UNION ALL
SELECT 2, 5, 'Sales' UNION ALL
SELECT 2, 6, 'Sales' UNION ALL
SELECT 2, 7, 'Sales' UNION ALL
SELECT 2, 8, 'Sales' UNION ALL
SELECT 2, 9, 'Sales' UNION ALL
SELECT 2, 10, 'Sales' UNION ALL
SELECT 1, 4, 'Sales'

SELECT * FROM #Email

UPDATE a
SET EMAIL_No = b.EMAIL_No
FROM #Email a
JOIN (
SELECT EMAIL_ID,
EMAIL_No = ROW_NUMBER() OVER (PARTITION BY PersonID, Department ORDER BY EMAIL_ID)
FROM #Email
) b
ON a.EMAIL_ID = b.EMAIL_ID

SELECT * FROM #Email

The first row of however many returned by the derived table will be used by the UPDATE. Only one UPDATE occurs regardless of how many rows match. The first row will have a value of 1 for EMAIL_NO.
There's no restriction in the derived table.
The self join ensures every row is touched.

Next question - what is this query supposed to do?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1016456
Posted Friday, November 5, 2010 5:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:34 AM
Points: 146, Visits: 124
Chris Morris-439714 (11/5/2010)


I reckon this will set the whole EMAIL_No column to 1



I agree that with the data you have specified the EMAIL_NO column will always be 1 because there aren’t any duplicate "PersonID, Department" entries. I guess the code only makes sense if individuals have multiple Email entries so PersonID values occur more than once in a department




--------------------------------------------------------------

“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
Post #1016462
Posted Friday, November 5, 2010 6:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
andy.roberts (11/5/2010)
Chris Morris-439714 (11/5/2010)


I reckon this will set the whole EMAIL_No column to 1



I agree that with the data you have specified the EMAIL_NO column will always be 1 because there aren’t any duplicate "PersonID, Department" entries. I guess the code only makes sense if individuals have multiple Email entries so PersonID values occur more than once in a department


Yep. I guess it's a bit easier to see with this:

;WITH CTEEmail AS (
SELECT EMAIL_ID,
EMAIL_No,
NewEMAIL_No = ROW_NUMBER() OVER (PARTITION BY PersonID, Department ORDER BY EMAIL_ID)
FROM #Email
) UPDATE CTEEmail SET EMAIL_No = NewEMAIL_No



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1016478
Posted Tuesday, January 25, 2011 10:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 23, 2011 8:57 AM
Points: 15, Visits: 46
Hi

I've only just realised that i never said thank you for your help with this query! So, thanks very much guys - much appreciated.
Post #1053339
Posted Thursday, July 31, 2014 11:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 31, 2014 11:13 AM
Points: 50, Visits: 6

In the Given Query a new column will create at run time with name EmailNumber and that number will update the variable where the SET statement have been used.
Post #1598400
Posted Friday, August 1, 2014 1:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:43 AM
Points: 6,890, Visits: 14,254
shashikantnist (7/31/2014)

In the Given Query a new column will create at run time with name EmailNumber and that number will update the variable where the SET statement have been used.


What variable? No variables are referenced in the last query.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1598602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse