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
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.
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.
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.
Try this:
SELECT MIN(id) AS id, title FROM tbl_countries GROUP BY title
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With