I am new to SQL queries, I have some data and I am trying to find the result which is shown below.

In my sample data, I have customer ID repeating multiple times due to multiple locations, What I am looking to do is create a query which gives output shown in image output format,

1. If customer exists only once I take that row
2. If customer exists more than once, I check the country; if Country = 'US', I take that ROW and discard others
3. If customer exists more than once and country is not US, then I pick the first row

What I have tried: I am trying to do this using rank function but was unsuccessful. Not sure if m,y approach is right, Please anyone share the T-SQL query for the problem.

Regards, Rahul

Sample data:

Output required :

CodePudding user response：

I have created a (short) dbfiddle

Short explanation (to just repeat the code here on SO):

Step1:

-- select everyting, and 'US' as first row
SELECT 
   cust_id, 
   country,
   sales,
   CASE WHEN country='US' THEN 0 ELSE 1 END X,
   ROW_NUMBER() OVER (PARTITION BY cust_id 
                      ORDER BY (CASE WHEN country='US' THEN 0 ELSE 1 END)) R
FROM table1
ORDER BY cust_id, CASE WHEN country='US' THEN 0 ELSE 1 END;

Step2:

-- filter only rows which are first row...
SELECT * 
FROM (
   SELECT 
      cust_id, 
      country,
      sales,
      CASE WHEN country='US' THEN 0 ELSE 1 END X,
      ROW_NUMBER() OVER (PARTITION BY cust_id 
                         ORDER BY (CASE WHEN country='US' THEN 0 ELSE 1 END)) R
   FROM table1
   -- ORDER BY cust_id, CASE WHEN country='US' THEN 0 ELSE 1 END
   ) x
WHERE x.R=1

CodePudding user response：

I can't vouch for performance but it should work on SQL Server 2005. Assuming your table is named CustomerData try this:

select cust_id, country, Name, Sales, [Group]
from CustomerData
where country = 'US'
union
select c.* from CustomerData c
join (
    select cust_id, min(country) country
    from CustomerData
    where cust_id not in (
        select cust_id
        from CustomerData
        where country = 'US'
        )
    group by cust_id
) a on a.cust_id = c.cust_id and a.country = c.country

It works by finding all those with a record with US as the country and then unioning that with the first country from every record that doesn't have the US as a country. If min() isn't getting the country you want then you'll need to find an alternative aggregation function that will select the country you want.