How to select the first row for each group in MySQL

Question

In C  it would be like this   table     GroupBy row   gt  row SomeColumn      Select group   gt  group         OrderBy row   gt  row AnotherColumn          First          Linq-To-Sql translates it to the following T-SQL code   SELECT  t3   AnotherColumn    t3   SomeColumn  FROM       SELECT  t0   SomeColumn      FROM  Table  AS  t0      GROUP BY  t0   SomeColumn        AS  t1  OUTER APPLY       SELECT TOP  1   t2   AnotherColumn    t2   SomeColumn      FROM  Table  AS  t2      WHERE    t1   SomeColumn  IS NULL  AND   t2   SomeColumn  IS NULL         OR    t1   SomeColumn  IS NOT NULL  AND   t2   SomeColumn  IS NOT NULL          AND   t1   SomeColumn     t2   SomeColumn        ORDER BY  t2   AnotherColumn        AS  t3  ORDER BY  t3   AnotherColumn    But it is incompatible with MySQL

User · Answer

SELECT     t1    FROM     table name AS t1      LEFT JOIN table name AS t2 ON           t2 group by column   t1 group by column         -- group by column is the column you would use in the GROUP BY statement         AND         t2 order by column  lt  t1 order by column         -- order by column is column you would use in the ORDER BY statement         -- usually is the autoincremented key column        WHERE     t2 group by column IS NULL    With MySQL v8  you could use window functions

User · Answer

From MySQL 5 7 documentation     MySQL 5 7 5 and up implements detection of functional dependence  If the ONLY FULL GROUP BY SQL mode is enabled  which it is by default   MySQL rejects queries for which the select list  HAVING condition  or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them    This means that  Jader Dias s solution wouldn t work everywhere   Here is a solution that would work when ONLY FULL GROUP BY is enabled   SET  row    NULL  SELECT     SomeColumn      AnotherColumn FROM       SELECT         CASE  id  lt   gt  SomeColumn AND  row IS NOT NULL              WHEN TRUE THEN  row     row 1              ELSE  row    0          END AS rownum           id    SomeColumn AS SomeColumn          AnotherColumn     FROM         SomeTable     ORDER BY         SomeColumn  -AnotherColumn DESC    values WHERE rownum   0 ORDER BY SomeColumn

User · Answer

Why not use MySQL LIMIT keyword   SELECT  t2   AnotherColumn    t2   SomeColumn  FROM  Table  AS  t2  WHERE    t1   SomeColumn  IS NULL  AND   t2   SomeColumn  IS NULL     OR    t1   SomeColumn  IS NOT NULL  AND   t2   SomeColumn  IS NOT NULL      AND   t1   SomeColumn     t2   SomeColumn    ORDER BY  t2   AnotherColumn  LIMIT 1

User · Answer

You should use some aggregate function to get the value of AnotherColumn that you want    That is  if you want the lowest value of AnotherColumn for each value of SomeColumn  either numerically or lexicographically   you can use   SELECT SomeColumn  MIN AnotherColumn  FROM YourTable GROUP BY SomeColumn   Some hopefully helpful links   http   dev mysql com doc refman 5 1 en group-by-functions html  http   www oreillynet com databases blog 2007 05 debunking group by myths html

User · Answer

Best performance and easy to use  SELECT id  code  SUBSTRING INDEX  GROUP CONCAT price ORDER BY id DESC        1  first found price FROM stocks GROUP BY code ORDER BY id DESC

User · Answer

Yet another way to do it  Select max from group that works in views  SELECT   FROM action a  WHERE NOT EXISTS      SELECT 1 FROM action a2     WHERE a2 user id   a user id     AND a2 action date  gt  a action date     AND a2 action type   a action type   AND a action type    CF

User · Answer

When I write  SELECT AnotherColumn FROM Table GROUP BY SomeColumn     It works  IIRC in other RDBMS such statement is impossible  because a column that doesn t belongs to the grouping key is being referenced without any sort of aggregation   This  quirk  behaves very closely to what I want  So I used it to get the result I wanted   SELECT   FROM     SELECT   FROM  table   ORDER BY AnotherColumn   t1 GROUP BY SomeColumn

User · Answer

I have not seen the following solution among the answers  so I thought I d put it out there   The problem is to select rows which are the first rows when ordered by AnotherColumn in all groups grouped by SomeColumn    The following solution will do this in MySQL  id has to be a unique column which must not hold values containing -  which I use as a separator    select t1   from mytable t1 inner join     select SUBSTRING INDEX      GROUP CONCAT t3 id ORDER BY t3 AnotherColumn DESC SEPARATOR  -         -        1     as id   from mytable t3   group by t3 SomeColumn   t2 on t2 id   t1 id   -- Where  SUBSTRING INDEX GROUP CONCAT id order by AnotherColumn desc separator  -     -   1  -- can be seen as  FIRST id order by AnotherColumn desc   -- For completeness sake  SUBSTRING INDEX GROUP CONCAT id order by AnotherColumn desc separator  -     -   -1  -- would then be seen as  LAST id order by AnotherColumn desc    There is a feature request for FIRST   and LAST   in the MySQL bug tracker  but it was closed many years back

User · Answer

I suggest to use this official way from MySql  SELECT article  dealer  price FROM   shop s1 WHERE  price  SELECT MAX s2 price                FROM shop s2               WHERE s1 article   s2 article               GROUP BY s2 article  ORDER BY article   With this way  we can get the highest price on each article

User · Answer

Yet another way to do it  without the primary key  would be using the JSON functions   select somecolumn  json unquote  json extract json arrayagg othercolumn      0        from sometable group by somecolumn   or pre 5 7 22  select somecolumn     json unquote       json extract         concat       group concat othercolumn separator                        0             from sometable group by somecolumn   Ordering  or filtering  can be done before grouping   select somecolumn  json unquote  json extract json arrayagg othercolumn      0         from  select   from sometable order by othercolumn  as t group by somecolumn       or after grouping  of course    select somecolumn  json unquote  json extract json arrayagg othercolumn      0      as other    from sometable group by somecolumn order by other   Admittedly  it s rather convoluted and performance is probably not great  didn t test it on large data  works well on my limited data sets

User · Answer

Here s another way you could try  that doesn t need that ID field      select some column  min another column    from i have a table  group by some column   Still I agree with lfagundes that you should add some primary key       Also beware that by doing this  you cannot  easily  get at the other values is the same row as the resulting some colum  another column pair  You d need lfagundes apprach and a PK to do that

User · Answer

Select the first row for each group  as ordered by a column  in Mysql     We have      a table  mytable a column we are ordering by  the column to order by a column that we wish to group by  the group by column  Here s my solution   The inner query gets you a unique set of rows  selected as a dual key  The outer query joins the same table by joining on both of those keys  with AND     SELECT   FROM                 SELECT the group by column  MAX the column to order by  the column to order by          FROM mytable          GROUP BY the group by column          ORDER BY MAX the column to order by  DESC        as mytable1  JOIN mytable mytable2 ON mytable2 the group by column    mytablealiamytable2 the group by column    AND mytable2 the column to order by   mytable1 the column to order by    FYI  I haven t thought about efficiency at all for this and can t speak to that one way or the other

User · Answer

How about this   SELECT SUBSTRING INDEX        MIN CONCAT OrderColumn       IFNULL TargetColumn                   -1  as TargetColumn FROM table GROUP BY GroupColumn

User · Answer

I based my answer on the title of your post only  as I don t know C  and didn t understand the given query  But in MySQL I suggest you try subselects  First get a set of primary keys of interesting columns then select data from those rows   SELECT somecolumn  anothercolumn    FROM sometable   WHERE id IN                  SELECT min id                    FROM sometable                  GROUP BY somecolumn

[mysql] How to select the first row for each group in MySQL?

Examples related to mysql

Examples related to group-by

Examples related to grouping