asked on
*** Need multi-part query using SQL RANK function - Tricky !!!!
Hello experts! I'm new here and hope you can help. This is a tricky problem I have:
I have a table of customers' phone numbers. A customer can have multiple phone numbers and multiple phone types (home, work, mobile, etc)... and any combination of such (2 home numbers and 1 work number, etc). They can designate which number is "preferred"... and each customer may have multiple preferred" numbers. Each record has a create date.
My problem:
I need a single row returned for each customer with a single preferred phone number.
- If a customer has one preferred phone number, then we will use that one.
- If they have multiple preferred phone numbers, then we use the most recent created one.
- If they have no preferred phone number(s) identfied, then we use the most recent created one.
I will upload a sample data file shortly (could not attach it directly to this post)
Thank you so much!!
I have a table of customers' phone numbers. A customer can have multiple phone numbers and multiple phone types (home, work, mobile, etc)... and any combination of such (2 home numbers and 1 work number, etc). They can designate which number is "preferred"... and each customer may have multiple preferred" numbers. Each record has a create date.
My problem:
I need a single row returned for each customer with a single preferred phone number.
- If a customer has one preferred phone number, then we will use that one.
- If they have multiple preferred phone numbers, then we use the most recent created one.
- If they have no preferred phone number(s) identfied, then we use the most recent created one.
I will upload a sample data file shortly (could not attach it directly to this post)
Thank you so much!!
Microsoft SQL ServerMicrosoft SQL Server 2005Oracle Database
Last Comment
giggul
here is an example, without knowing exactly how favorite is stored - the CASE might not be needed.
SELECT * FROM
(select *, row_number() OVER (PARTITION BY CustomerId ORDER BY
CASE WHEN Favourite = 'Y' THEN 1 ELSE 0 END DESC,
CreateDate DESC) as rowN) DQ
WHERE DQ.rowN = 1
SELECT * FROM
(select *, row_number() OVER (PARTITION BY CustomerId ORDER BY
CASE WHEN Favourite = 'Y' THEN 1 ELSE 0 END DESC,
CreateDate DESC) as rowN) DQ
WHERE DQ.rowN = 1
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I'm unable to download your file for security reasons, but I would try something along the lines of:
SELECT Top 1 C.CustomerID, CP.PhoneNumber
FROM tbl_Customers as C
INNER JOIN tbl_CustomerPhones as CP
ON C.CustomerID = CP.CustomerID
WHERE C.CustomerID = [Customer ID]
ORDER BY CP.Preferred ASC, CP.Updated Desc
SELECT Top 1 C.CustomerID, CP.PhoneNumber
FROM tbl_Customers as C
INNER JOIN tbl_CustomerPhones as CP
ON C.CustomerID = CP.CustomerID
WHERE C.CustomerID = [Customer ID]
ORDER BY CP.Preferred ASC, CP.Updated Desc
ASKER
deighton,
I can't get the code to run using my table and fields. Getting error "FROM keyword not found"
I can't get the code to run using my table and fields. Getting error "FROM keyword not found"
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks everyone! I will test out all these suggestions on my data and let you know results.
ASKER
lwadwell and deighton,
Your codes work flawlessly!! I see that one uses RANK and one uses ROW_NUMBER. In a case where there may be a tie (two records with the exact same create_date), would row_number be better to use? I realize I didn't pose that scenario in my original question.
fyed,
I am getting error "FROM keyword not found..."
Thanks all !!!
Your codes work flawlessly!! I see that one uses RANK and one uses ROW_NUMBER. In a case where there may be a tie (two records with the exact same create_date), would row_number be better to use? I realize I didn't pose that scenario in my original question.
fyed,
I am getting error "FROM keyword not found..."
Thanks all !!!
Yes - I did suggest row_number() was the better choice for that reason - I only gave rank() in the SQL as you explicitly asked for it.
@giggul,
Disregard my suggestion; there are several issues with my post, starting with the part that I missed you wanted that phone number for each customer. Then there is also the fact that I wrote the query for Access, not SQL Server (must have been a caffeine deficiency).
Disregard my suggestion; there are several issues with my post, starting with the part that I missed you wanted that phone number for each customer. Then there is also the fact that I wrote the query for Access, not SQL Server (must have been a caffeine deficiency).
in the event of a tie, RANK() returns two rows, both with 1
in the same case, row_number() will return either of the tied rows as row=1 - in both cases the results are identical if data is unique and ties are impossible
if genuine ties are possible (e.g. top salesaman of the year, you need to decide how to handle that, or provide a unique tie breaker)
in the same case, row_number() will return either of the tied rows as row=1 - in both cases the results are identical if data is unique and ties are impossible
if genuine ties are possible (e.g. top salesaman of the year, you need to decide how to handle that, or provide a unique tie breaker)
ASKER
lwadwell,
So I assume it wouldn't be a problem to use ROW_NUMBER in place of RANK in your query, and the results are still accurate.
Thanks again!!!
So I assume it wouldn't be a problem to use ROW_NUMBER in place of RANK in your query, and the results are still accurate.
Thanks again!!!
correct - just replace rank() with row_number(); the over() clause stays the same.
ASKER
Thanks for your help guys! This has been a great first experience on EE for me! I will split the points evenly since both methods work well!
ASKER
The experts were very quick and knowledegable!!!
https://filedb.experts-exchange.com/incoming/ee-stuff/8261-sample.xls