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

Question

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

User · Accepted Answer

Try this   SELECT MIN id  AS id  title FROM tbl countries GROUP BY title

User · Answer

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

User · Answer

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  gt 1    GROUP BY itemcode

User · Answer

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

User · Answer

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

User · Answer

Duplicate rows can be removed for Complex Queries by    First storing the result to a  TempTable or  TempTableVariable  Delete from  TempTable or  TempTableVariable where your condition  Then select the rest of the data   If need to create a row number create an identity column

User · Answer

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

User · Answer

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  gt  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  gt  t2 Country id

User · Answer

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

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

Examples related to sql

Examples related to select

Examples related to duplicates

Examples related to distinct