'How to select all attributes (*) with distinct values in a particular column(s)?

Here is link to the w3school database for learners:

W3School Database

If we execute the following query:

SELECT DISTINCT city FROM Customers

it returns us a list of different City attributes from the table.

What to do if we want to get all the rows like that we get from SELECT * FROM Customers query, with unique value for City attribute in each row.

sql


Solution 1:[1]

DISTINCT when used with multiple columns, is applied for all the columns together. So, the set of values of all columns is considered and not just one column.

If you want to have distinct values, then concatenate all the columns, which will make it distinct.

Or, you could group the rows using GROUP BY.

Solution 2:[2]

You need to select all values from customers table, where city is unique. So, logically, I came with such query:

SELECT * FROM `customers` WHERE `city` in (SELECT DISTINCT `city` FROM `customers`)

Solution 3:[3]

I think you want something like this:
(change PK field to your Customers Table primary key or index like Id)

In SQL Server (and standard SQL)

SELECT 
    *
FROM (
    SELECT 
        *, ROW_NUMBER() OVER (PARTITION BY City ORDER BY PK) rn
    FROM
        Customers ) Dt
WHERE
    (rn = 1)

In MySQL 

SELECT 
    * 
FORM (
    SELECT 
        a.City, a.PK, count(*) as rn 
    FROM 
        Customers a
        JOIN 
        Customers b ON a.City = b.City AND a.PK >= b.PK
    GROUP BY a.City, a.PK ) As DT
WHERE (rn = 1)

This query -I hope - will return your Cities distinctly and also shows other columns.

Solution 4:[4]

You can use GROUP BY clause for getting distinct values in a particular column. Consider the following table - 'contact':

+---------+------+---------+
| id      | name | city    |
+---------+------+---------+
| 1       | ABC  | Chennai |
+---------+------+---------+
| 2       | PQR  | Chennai |
+---------+------+---------+
| 3       | XYZ  | Mumbai  |
+---------+------+---------+

To select all columns with distinct values in City attribute, use the following query:

SELECT * FROM contact GROUP BY city;

This will give you the output as follows:

+---------+------+---------+
| id      | name | city    |
+---------+------+---------+
| 1       | ABC  | Chennai |
+---------+------+---------+
| 3       | XYZ  | Mumbai  |
+---------+------+---------+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lalit Kumar B
Solution 2 Drew
Solution 3 a_horse_with_no_name
Solution 4 GreyWalker

Related Questions