Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 
like image 547
Mohammad Saberi Avatar asked Sep 02 '12 19:09

Mohammad Saberi


People also ask

How do I select a record without duplicates in one column in SQL?

If you want the query to return only unique rows, use the keyword DISTINCT after SELECT . DISTINCT can be used to fetch unique rows from one or more columns. You need to list the columns after the DISTINCT keyword.

How do I ignore duplicate records in SQL while selecting query?

Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.

How do I remove duplicates from one column in SQL?

Introduction to SQL DISTINCT operator Note that the DISTINCT only removes the duplicate rows from the result set. It doesn't delete duplicate rows in the table. If you want to select two columns and remove duplicates in one column, you should use the GROUP BY clause instead.


2 Answers

Try this:

SELECT MIN(id) AS id, title FROM tbl_countries GROUP BY title 
like image 152
Mark Byers Avatar answered Oct 06 '22 00:10

Mark Byers


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.

like image 28
Immu Avatar answered Oct 06 '22 01:10

Immu