SQL query all register but select only one of one column

Question

Assume that I have one table looking like this:

ID	ClientID	Name	Country
1	JX100	John	Canada
2	JX100	John	Japan
3	JX690	Rob	EUA
4	PX301	Alice	France

And My query:

SELECT DISTINCT ClientID,Name,Country FROM CLIENTS

OUTPUT:

ClientID	Name	Country
JX100	John	Canada
JX100	John	Japan
JX690	Rob	EUA
PX301	Alice	France

I want to take that:

DESIRED:

ClientID	Name	Country
JX100	John	Canada
JX690	Rob	EUA
PX301	Alice	France

TL;DR

I just want to select one country for all Clients, I don't want to get repeated rows.

There are not repeated rows in your output though. John is registered(?) to 2 countries — Thom A, Mar 03 '21 at 17:18
Just care about the top one of the countries. The row is not entire repeated @Larnu — Renan Duarte, Mar 03 '21 at 17:27

Kazi Mohammad Ali Nur Romel · Answer 1 · 2021-03-03T17:28:36.293

You can use row_number()over() with common table expression like below:

with cte as (
SELECT DISTINCT ClientID,Name,Country,row_number()over(partition by  ClientID   ,Name    order by Country) rownumber FROM CLIENTS)
select * from cte where rownumber=1

Row_number()over(partition by ClientID ,Name order by Country) will generate a sequence for contries starting from 1 against each clientid and name. So when you will select rownumber=1 this query will select one country for a single clientid,name combination.

SQL query all register but select only one of one column

1 Answers1