Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – How to select records without duplicate records

I have a table with 3 columns like this:

+---------+--------+-------+
| Country | Number | State |
+---------+--------+-------+
| Canada  | 12     | 0     |
+---------+--------+-------+
| Canada  | 14     | 0     |
+---------+--------+-------+
| USA     | 10     | 2     |
+---------+--------+-------+
| Germany | 8      | 1     |
+---------+--------+-------+

These are many records in this table.
I need to have country names without any duplikate. I used DISTINCT in the from below but I could not achieve an appropriate result.

SELECT DISTINCT country, number, state FROM tbl_country;

My desired result is something like this (only unique country names):

+---------+--------+-------+
| USA     | 10     | 2     |
+---------+--------+-------+
| Germany | 8      | 1     |
+---------+--------+-------+
Tags:

2

Answers


  1. 0

    If you are using mysql 8, you can use the window function COUNT() to compute the count for each country, and then filter the results to include only those with a count of 1.

    SELECT Country, Number, State
FROM (
  SELECT *, count(*) OVER (PARTITION BY Country ) as cnt
  FROM mytable
) AS s
WHERE cnt = 1;

    Alternatively, you can do it by using GROUP BY and HAVING clauses, along with the aggregate function MAX() :

    SELECT Country, MAX(Number) AS Number, MAX(State) AS State
FROM mytable
GROUP BY Country
HAVING COUNT(*) = 1
    Login or Signup to reply.
  2. 0

    by using a subquery, you can achieve your desired output.
    First group by country and count how many appearances there are and then filter
    to get the countries that are in the subquery. Later get the final output

    SELECT country, number, state
FROM tbl_country
WHERE country IN (
    SELECT country 
    FROM tbl_country 
    GROUP BY country 
    HAVING COUNT(country) = 1
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search