How to get rid of duplicate values base on one column in SQL

Question

I have a simple query that tries to select a distinct Country column from the Customer table, but it is returning duplicate values.

Here is the table looks like

Country     City        PostalCode  CustomerName                    ContactName
USA         Lander      82520       Split Rail Beer & Ale           Art Braunschweiger
Germany     Leipzig     4179        Morgenstern Gesundkost          Alexander Feuer
France      Lille       59000       Folies gourmandes               Martine Rancé
Portugal    Lisboa      1675        Furia Bacalhau e                Lino Rodriguez
Portugal    Lisboa      1756        Princesa Isabel Vinhoss         Isabel de Castro
UK          London      WA11DP      Around the Horn                 Thomas Hardy
UK          London      EC25NT      B's Beverages                   Victoria Ashworth
UK          London      WX16LT      Consolidated Holdings           Elizabeth Brown
UK          London      WX36FW      Eastern Connection              Ann Devon

I want to get only distinct country name and it doesn't matter other column result. So I'm running below SQL code but it keep giving me some duplicate values.


with all_data as (SELECT distinct Country , City, PostalCode, CustomerName,ContactName FROM Customers),

distinct_country as (
SELECT distinct Country FROM Customers
),
distinct_values as (
SELECT t.Country , a.City, a.PostalCode, a.CustomerName,a.ContactName 
FROM distinct_country t
left join 
all_data a
on t.Country = a.Country
)
select * from distinct_values

I'm looking like the below result. (the distinct country name and it doesn't matter other column result)

Country     City        PostalCode  CustomerName                    ContactName
USA         Lander      82520       Split Rail Beer & Ale           Art Braunschweiger
Germany     Leipzig     4179        Morgenstern Gesundkost          Alexander Feuer
France      Lille       59000       Folies gourmandes               Martine Rancé
Portugal    Lisboa      1675        Furia Bacalhau e                Lino Rodriguez
UK          London      WA11DP      Around the Horn                 Thomas Hardy

How can I do that? Thanks in advance!

You said you don't care the other fields but you expected them. Which is the criteria to choose WA11DP for London and not WX36FW ? — Leandro Bardelli, Apr 07 '23 at 17:27
And you are using mysql OR sql-server? Please don't tag several DBMS — Leandro Bardelli, Apr 07 '23 at 17:33

score 1 · Answer 1 · answered Apr 07 '23 at 17:31

1

If you only need the countries and the other fields doesn't care, just select that one and remove duplicates with GROUP BY

SELECT 
Country 
FROM all_data
GROUP BY Country

If you need to show the other fields, you have to put some criteria for the GROUP BY

answered Apr 07 '23 at 17:31

Leandro Bardelli

10,561
15
79
116

Yep, that's where I'm struggling. I need at least one set of data from each column too. But it doesn't matter which row it coming from. Do you know how to add that to here? – rra Apr 07 '23 at 17:50
@rra just in order to update my answer, do you need real data at random? Because you could just use dummy data if you need the columns. – Leandro Bardelli Apr 07 '23 at 17:53
Not dummy data it need to come from those coulmns – rra Apr 07 '23 at 17:56
@rra ok, but if you need the top 1 this could change by your DBMS, you should specify what you would use – Leandro Bardelli Apr 07 '23 at 18:01
1

@rra this question helps you with several systems: https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group – Leandro Bardelli Apr 07 '23 at 18:03
Thanks, I'm using Hiveql – rra Apr 07 '23 at 18:06
I can use either row_number() or max() function from the above link. Thanks – rra Apr 07 '23 at 18:10

score 1 · Accepted Answer · answered Apr 07 '23 at 18:08

You haven't said what dbms you're on. This works on SQL Server.

ROW_NUMBER is the magic you're looking for. The CTE adds a row number that counts up on each row but starts over for each country. You then select only the rows where the row number is 1.

FIDDLE

;WITH CTE AS
  (SELECT Country
        , City
        , PostalCode
        , CustomerName
        , ContactName
        , ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) rn
     FROM data 
  )
SELECT Country
     , City
     , PostalCode
     , CustomerName
     , ContactName
  FROM CTE
 WHERE rn = 1

Country	City	PostalCode	CustomerName	ContactName
France	Lille	59000	Folies gourmandes	Martine Rancé
Germany	Leipzig	4179	Morgenstern Gesundkost	Alexander Feuer
Portugal	Lisboa	1675	Furia Bacalhau e	Lino Rodriguez
UK	London	WA11DP	Around the Horn	Thomas Hardy
USA	Lander	82520	Split Rail Beer & Ale	Art Braunschweiger

How to get rid of duplicate values base on one column in SQL

2 Answers2