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

sql select duplicates distinct
Mohammad Saberi picture Mohammad Saberi · Sep 2, 2012 · Viewed 414.1k times · Source

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

Answer

Mark Byers picture Mark Byers · Sep 2, 2012

Try this:

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

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL SELECT WHERE field contains words

I need a select which would return results like this: SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3' And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More