MySQL select one column DISTINCT with corresponding other columns

Question

ID   FirstName   LastName 1      John        Doe 2      Bugs        Bunny 3      John        Johnson   I want to select DISTINCT results from the FirstName column  but I need the corresponding ID and LastName   The result set needs to show only one John  but with an ID of 1 and a LastName of Doe

User · Answer

Keep in mind when using the group by and order by that MySQL is the ONLY database that allows for columns to be used in the group by and/or order by piece that are not part of the select statement.

So for example: select column1 from table group by column2 order by column3

That will not fly in other databases like Postgres, Oracle, MSSQL, etc. You would have to do the following in those databases

select column1, column2, column3 from table group by column2 order by column3

Just some info in case you ever migrate your current code to another database or start working in another database and try to reuse code.

User · Answer

SELECT DISTINCT  column1   column2 FROM table1 GROUP BY column1

User · Answer

The DISTINCT keyword doesn t really work the way you re expecting it to  When you use SELECT DISTINCT col1  col2  col3 you are in fact selecting all unique  col1  col2  col3  tuples

User · Answer

To avoid potentially unexpected results when using GROUP BY without an aggregate function  as is used in the accepted answer  because MySQL is free to retrieve ANY value within the data set being grouped when not using an aggregate function  sic  and issues with ONLY FULL GROUP BY  Please consider using an exclusion join   Exclusion Join - Unambiguous Entities  Assuming the firstname and lastname are uniquely indexed  unambiguous   an alternative to GROUP BY is to sort using a LEFT JOIN to filter the result set  otherwise known as an exclusion JOIN    See Demonstration  Ascending order  A-Z   To retrieve the distinct firstname ordered by lastname from A-Z  Query  SELECT t1   FROM table name AS t1 LEFT JOIN table name AS t2 ON t1 firstname   t2 firstname AND t1 lastname  gt  t2 lastname WHERE t2 id IS NULL    Result    id   firstname   lastname    ---- ----------- ----------     2        Bugs      Bunny      1        John        Doe     Descending order  Z-A   To retrieve the distinct firstname ordered by lastname from Z-A  Query  SELECT t1   FROM table name AS t1 LEFT JOIN table name AS t2 ON t1 firstname   t2 firstname AND t1 lastname  lt  t2 lastname WHERE t2 id IS NULL    Result    id   firstname   lastname    ---- ----------- ----------     2        Bugs      Bunny      3        John    Johnson     You can then order the resulting data as desired     Exclusion Join - Ambiguous Entities  If the first and last name combination are not unique  ambiguous  and you have multiple rows of the same values  you can filter the result set by including an OR condition on the JOIN criteria to also filter by id    See Demonstration  table name data   1   John    Doe     2   Bugs    Bunny     3   John    Johnson     4   John    Doe     5   John    Johnson     Query  SELECT t1   FROM table name AS t1 LEFT JOIN table name AS t2 ON t1 firstname   t2 firstname AND  t1 lastname  gt  t2 lastname OR  t1 firstname   t1 firstname AND t1 lastname   t2 lastname AND t1 id  gt  t2 id   WHERE t2 id IS NULL    Result    id   firstname   lastname    ---- ----------- ----------     1        John        Doe      2        Bugs      Bunny       Ordered Subquery  EDIT  My original answer using an ordered subquery  was written prior to MySQL 5 7 5  which is no longer applicable  due to the changes with ONLY FULL GROUP BY   Please use the exclusion join examples above instead   It is also important to note  when ONLY FULL GROUP BY is disabled  original behavior prior to MySQL 5 7 5   the use of GROUP BY without an aggregate function may yield unexpected results  because MySQL is free to choose ANY value within the data set being grouped  sic     Meaning an ID or lastname value may be retrieved that is not associated with the retrieved firstname row     WARNING  With MySQL GROUP BY may not yield the expected results when used with ORDER BY   See Test Case Example  The best method of implementation  to ensure expected results  is to filter the result set scope using an ordered subquery   table name data   1   John    Doe     2   Bugs    Bunny     3   John    Johnson     Query  SELECT   FROM       SELECT   FROM table name ORDER BY ID DESC   AS t1 GROUP BY FirstName   Result    ID   first      last    ---- ------- ---------     2    Bugs     Bunny      3    John   Johnson       Comparison  To demonstrate the unexpected results when using GROUP BY in combination with ORDER BY  Query  SELECT   FROM table name GROUP BY FirstName ORDER BY ID DESC   Result    ID   first    last    ---- ------- -------     2    Bugs   Bunny      1    John     Doe

User · Answer

As pointed out by fyrye  the accepted answer pertains to older versions of MySQL in which ONLY FULL GROUP BY had not yet been introduced  With MySQL 8 0 17  used in this example   unless you disable ONLY FULL GROUP BY you would get the following error message   mysql gt  SELECT id  firstName  lastName FROM table name GROUP BY firstName       ERROR 1055  42000   Expression  1 of SELECT list is not in GROUP BY clause and contains nonaggregated column  mydatabase table name id  which is not functionally dependent on columns in GROUP BY clause  this is incompatible with sql mode only full group by   One way to work around this not mentioned by fyrye  but described in https   dev mysql com doc refman 5 7 en group-by-handling html  is to apply the ANY VALUE   function to the columns which are not in the GROUP BY clause  id and lastName in this example    mysql gt  SELECT ANY VALUE id  as id  firstName  ANY VALUE lastName  as lastName FROM table name GROUP BY firstName   ---- ----------- ----------    id   firstName   lastName    ---- ----------- ----------     1   John        Doe           2   Bugs        Bunny       ---- ----------- ----------  2 rows in set  0 01 sec    As written in the aforementioned docs      In this case  MySQL ignores the nondeterminism of address values within each name group and accepts the query  This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group  ANY VALUE   is not an aggregate function  unlike functions such as SUM   or COUNT    It simply acts to suppress the test for nondeterminism

User · Answer

SELECT firstName  ID  LastName from tableName GROUP BY firstName

User · Answer

try this query   SELECT ID  FirstName  LastName FROM table GROUP BY FirstName

User · Answer

How about   SELECT      my distinct column      max col1       max col2       max col3           FROM     my table   GROUP BY      my distinct column

User · Answer

SELECT ID LastName  From TABLE NAME  GROUP BY FirstName  HAVING COUNT     gt  1

User · Answer

Not sure if you can do this with MySQL  but you can use a CTE in T-SQL    WITH tmpPeople AS    SELECT     DISTINCT FirstName      MIN Id         FROM People   SELECT  tP Id   tP FirstName   P LastName FROM tmpPeople tP JOIN People P ON tP Id   P Id   Otherwise you might have to use a temporary table

User · Answer

SELECT DISTINCT firstName   ID  LastName from tableName GROUP BY firstName   Would be the best bet IMO

User · Answer

You can use group by for display distinct values and also corresponding fields   select   from tabel name group by FirstName   Now you got output like this   ID    FirstName     LastName 2     Bugs          Bunny 1     John          Doe    If you want to answer like  ID    FirstName     LastName 1     John          Doe 2     Bugs          Bunny   then use this query   select   from table name group by FirstName order by ID

[mysql] MySQL select one column DISTINCT, with corresponding other columns

Examples related to mysql