CODEWITHSUNDEEP
sqlselectduplicatesdistinct
Mohammad Saberi
Mohammad Saberi

Reputation: 13166

How to select records without duplicate on just one field in SQL?

I have a table with 3 columns like this: 

+------------+---------------+-------+  
| Country_id | country_title | State |  
+------------+---------------+-------+

There are many records in this table. Some of them have state and some other don't. Now, imagine these records: 

1 | Canada  | Alberta  
2 |  Canada | British  Columbia  
3 | Canada  | Manitoba  
4 | China   |

I need to have country names without any duplicate. Actually I need their id and title, What is the best SQL command to make this? I used DISTINCT in the form below but I could not achieve an appropriate result. 

SELECT DISTINCT title,id FROM tbl_countries ORDER BY title

My desired result is something like this: 

1, Canada  
4, China

Upvotes: 82

Views: 622015

Answers (8)

user6678173
user6678173

Reputation:

In MySQL a special column function GROUP_CONCAT can be used:

SELECT GROUP_CONCAT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'computers' AND
    TABLE_NAME='Laptop' AND
    COLUMN_NAME NOT IN ('code')
ORDER BY ORDINAL_POSITION;

It should be mentioned that the information schema in MySQL covers all database server, not certain databases. That is why if different databases contains tables with identical names, search condition of the WHERE clause should specify the schema name: TABLE_SCHEMA='computers'.

Strings are concatenated with the CONCAT function in MySQL. The final solution of our problem can be expressed in MySQL as:

SELECT CONCAT('SELECT ',
(SELECT GROUP_CONCAT(COLUMN_NAME)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA='computers' AND
    TABLE_NAME='Laptop' AND
        COLUMN_NAME NOT IN ('code')
 ORDER BY ORDINAL_POSITION
), ' FROM Laptop');

http://www.sql-ex.ru/help/select20.php

Upvotes: 1

shA.t
shA.t

Reputation: 16958

For using DISTINCT keyword, you can use it like this:

SELECT DISTINCT 
    (SELECT min(ti.Country_id) 
     FROM tbl_countries ti 
     WHERE t.country_title = ti.country_title) As Country_id
    , country_title
FROM 
    tbl_countries t

For using ROW_NUMBER(), you can use it like this:

SELECT 
    Country_id, country_title 
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY country_title ORDER BY Country_id) As rn
    FROM tbl_countries) t
WHERE rn = 1

Also with using LEFT JOIN, you can use this:

SELECT t1.Country_id, t1.country_title
FROM tbl_countries t1
    LEFT OUTER JOIN
    tbl_countries t2 ON t1.country_title = t2.country_title AND t1.Country_id > t2.Country_id
WHERE
    t2.country_title IS NULL

And with using of EXISTS, you can try:

SELECT t1.Country_id, t1.country_title
FROM tbl_countries t1   
WHERE
    NOT EXISTS (SELECT 1 
                FROM tbl_countries t2 
                WHERE t1.country_title = t2.country_title AND t1.Country_id > t2.Country_id)

Upvotes: 19

Ashah
Ashah

Reputation: 11

select Country_id,country_title from(
   select Country_id,country_title,row_number() over (partition by country_title 
   order by Country_id  ) rn from country)a
   where rn=1;

Upvotes: 1

Selim Reza
Selim Reza

Reputation: 1084

Ignore duplicate rows in SQL. I think this may help you.

    SELECT res2.*
    FROM
    (SELECT res1.*,ROW_NUMBER() OVER(PARTITION BY res1.title ORDER BY res1.id)as num
     FROM 
    (select * from [dbo].[tbl_countries])as res1
    )as res2
    WHERE res2.num=1

Upvotes: 0

M. Salem
M. Salem

Reputation: 21

Try this one

SELECT country_id, country_title 
FROM (SELECT country_id, country_title,
CASE
WHEN country_title=LAG(country_title, 1, 0) OVER(ORDER BY country_title) THEN 1
ELSE 0
END AS "Duplicates"
FROM tbl_countries)
WHERE "Duplicates"=0;

Upvotes: 2

SUERSH K
SUERSH K

Reputation: 15

Having Clause is the easiest way to find duplicate entry in Oracle and using rowid we can remove duplicate data..

DELETE FROM products WHERE rowid IN (
  SELECT MAX(sl) FROM (
  SELECT itemcode, (rowid) sl FROM products WHERE itemcode IN (
  SELECT itemcode FROM products GROUP BY itemcode HAVING COUNT(itemcode)>1
)) GROUP BY itemcode);

Upvotes: -2

Immu
Immu

Reputation: 786

DISTINCT is the keyword
For me your query is correct

Just try to do this first

SELECT DISTINCT title,id FROM tbl_countries

Later on you can try with order by.

Upvotes: 53

Mark Byers
Mark Byers

Reputation: 837936

Try this:

SELECT MIN(id) AS id, title
FROM tbl_countries
GROUP BY title

Upvotes: 131

Related Questions