Link to home
Start Free TrialLog in
Avatar of giggul
giggul

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!!
Avatar of giggul
giggul

ASKER

The sample data file and desired result is located here:

https://filedb.experts-exchange.com/incoming/ee-stuff/8261-sample.xls
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
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of giggul

ASKER

deighton,
I can't get the code to run using my table and fields.  Getting error "FROM keyword not found"
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of giggul

ASKER

Thanks everyone!  I will test out all these suggestions on my data and let you know results.
Avatar of giggul

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 !!!
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).
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)
Avatar of giggul

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!!!
correct - just replace rank() with row_number(); the over() clause stays the same.
Avatar of giggul

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!
Avatar of giggul

ASKER

The experts were very quick and knowledegable!!!