May 29, 2017 at 5:38 pm
Hi,
Below is my sample code.
What I want to loop through RN# and find the max(Cust_id) is my Max_id and use until I have the same email.
Please let me know if my question is not clear.
Thank You.
CREATE TABLE table1
(
Email VARCHAR(50),
Cus_id INT
)
INSERT INTO dbo.table1
( Email, Cus_id )
Select'abc@hotmail.com',456
UNION
SELECT 'abc@hotmail.com',923
UNION
SELECT 'abc@hotmail.com',921
UNION
SELECT 'james@yahoo.com',1002
UNION
SELECT 'james@yahoo.com',1009
UNION
SELECT 'james@yahoo.com',1067
UNION
Select'Smith@hotmail.com',456
UNION
SELECT 'abc@hotmail.com',123
SELECT
*,Rn = ROW_NUMBER() OVER(PARTITION BY email ORDER BY email ASC)
FROM dbo.table1
The END RESULT what I want IS
Max_cus_id,Cus_id,rn
923,123,1
923,456,2
923,921,3
1067,1002,1
1067,1009,2
May 29, 2017 at 6:49 pm
rocky_498 - Monday, May 29, 2017 5:38 PMHi,
Below is my sample code.
What I want to loop through RN# and find the max(Cust_id) is my Max_id and use until I have the same email.
Please let me know if my question is not clear.Thank You.
CREATE TABLE table1
(
Email VARCHAR(50),
Cus_id INT
)INSERT INTO dbo.table1
( Email, Cus_id )
Select'abc@hotmail.com',456
UNION
SELECT 'abc@hotmail.com',923
UNION
SELECT 'abc@hotmail.com',921
UNION
SELECT 'james@yahoo.com',1002
UNION
SELECT 'james@yahoo.com',1009
UNION
SELECT 'james@yahoo.com',1067
UNION
Select'Smith@hotmail.com',456
UNION
SELECT 'abc@hotmail.com',123SELECT
*,Rn = ROW_NUMBER() OVER(PARTITION BY email ORDER BY email ASC)
FROM dbo.table1The END RESULT what I want IS
Max_cus_id,Cus_id,rn
923,123,1
923,456,2
923,921,3
1067,1002,1
1067,1009,2
I think I understand your question. Will this work for you?
WITH cteMaxes AS (
SELECT email, MaxID = MAX(cus_id)
FROM dbo.table1
GROUP BY email
)
SELECT m.MaxID, t.cus_id, RN = ROW_NUMBER() OVER(PARTITION BY m.MaxID ORDER BY t.cus_id)
FROM dbo.table1 t
CROSS APPLY cteMaxes m
WHERE m.email = t.email
AND NOT m.MaxID = t.Cus_id
ORDER BY m.MaxID;
May 29, 2017 at 7:55 pm
That is exactly what I am looking. Tomorrow I will run above code in the Live data and give you update. Looks good to me.
Thank You.
May 30, 2017 at 1:18 am
Quick thought, Ed's fine code can be made more efficient by two minor changes if you are running SQL Server 2012 or later. If you are on SQL Server 2005/2008 then only the first change applies.
😎
The first one is to add a POC index to eliminate the sorting for the MAX functionCREATE NONCLUSTERED INDEX NCLIDX_DBO_TABLE1_EMAIL_CUS_ID ON dbo.table1 (Email ASC, Cus_id ASC);
The POC index will benefit the original query by removing one out of two sort operators.
The second one is to eliminate the ROW_NUMBER sorting by aligning the two in a single step;WITH BASE_DATA AS
(
SELECT
MAX(T1.Cus_id) OVER
(
PARTITION BY T1.Email
) AS MaxID
,T1.Cus_id AS Cus_id
,ROW_NUMBER() OVER
(
PARTITION BY T1.Email
ORDER BY T1.Cus_id
) AS RN
FROM dbo.table1 T1
)
SELECT
BD.MaxID
,BD.Cus_id
,BD.RN
FROM BASE_DATA BD
WHERE BD.Cus_id < BD.MaxID;
This reduces the workload of the query by close to 80%.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply