MySQL order by before group by

Question

There are plenty of similar questions to be found on here but I don t think that any answer the question adequately   I ll continue from the current most popular question and use their example if that s alright   The task in this instance is to get the latest post for each author in the database   The example query produces unusable results as its not always the latest post that is returned   SELECT wp posts   FROM wp posts     WHERE wp posts post status  publish      AND wp posts post type  post      GROUP BY wp posts post author                ORDER BY wp posts post date DESC   The current accepted answer is   SELECT     wp posts   FROM wp posts WHERE     wp posts post status  publish      AND wp posts post type  post  GROUP BY wp posts post author HAVING wp posts post date   MAX wp posts post date   lt - ONLY THE LAST POST FOR EACH AUTHOR ORDER BY wp posts post date DESC   Unfortunately this answer is plain and simple wrong and in many cases produces less stable results than the orginal query   My best solution is to use a subquery of the form  SELECT wp posts   FROM        SELECT        FROM wp posts     ORDER BY wp posts post date DESC   AS wp posts WHERE wp posts post status  publish  AND wp posts post type  post  GROUP BY wp posts post author    My question is a simple one then  Is there anyway to order rows before grouping without resorting to a subquery   Edit  This question was a continuation from another question and the specifics of my situation are slightly different  You can  and should  assume that there is also a wp posts id that is a unique identifier for that particular post

User · Answer

Just to recap  the standard solution uses an uncorrelated subquery and looks like this    SELECT x     FROM my table x   JOIN  SELECT grouping criteria MAX ranking criterion  max n FROM my table GROUP BY grouping criteria  y     ON y grouping criteria   x grouping criteria    AND y max n   x ranking criterion    If you re using an ancient version of MySQL  or a fairly small data set  then you can use the following method   SELECT x     FROM my table x   LEFT   JOIN my table y     ON y joining criteria   x joining criteria    AND y ranking criteria  lt  x ranking criteria  WHERE y some non null column IS NULL

User · Answer

Using an ORDER BY in a subquery is not the best solution to this problem     The best solution to get the max post date  by author is to use a subquery to return the max date and then join that to your table on both the post author and the max date   The solution should be   SELECT p1    FROM wp posts p1 INNER JOIN       SELECT max post date  MaxPostDate  post author     FROM wp posts     WHERE post status  publish         AND post type  post      GROUP BY post author   p2   ON p1 post author   p2 post author   AND p1 post date   p2 MaxPostDate WHERE p1 post status  publish    AND p1 post type  post  order by p1 post date desc   If you have the following sample data   CREATE TABLE wp posts       id  int   title  varchar 6    post date  datetime   post author  varchar 3      INSERT INTO wp posts       id    title    post date    post author   VALUES      1   Title1    2013-01-01 00 00 00    Jim         2   Title2    2013-02-01 00 00 00    Jim       The subquery is going to return the max date and author of   MaxPostDate   Author 2 1 2013      Jim   Then since you are joining that back to the table  on both values you will return the full details of that post   See SQL Fiddle with Demo   To expand on my comments about using a subquery to accurate return this data    MySQL does not force you to GROUP BY every column that you include in the SELECT list   As a result  if you only GROUP BY one column but return 10 columns in total  there is no guarantee that the other column values which belong to the post author that is returned   If the column is not in a GROUP BY MySQL chooses what value should be returned   Using the subquery with the aggregate function will guarantee that the correct author and post is returned every time    As a side note  while MySQL allows you to use an ORDER BY in a subquery and allows you to apply a GROUP BY to not every column in the SELECT list this behavior is not allowed in other databases including SQL Server

User · Answer

No  It makes no sense to order the records before grouping  since grouping is going to mutate the  result set  The subquery way is the preferred way  If this is going too slow you would have to change your table design  for example by storing the id of of the last post for each author in a seperate table  or introduce a boolean column indicating for each author which of his post is the last one

User · Answer

First  don t use   in select  affects their performance and hinder the use of the group by and order by  Try this query   SELECT wp posts post author  wp posts post date as pdate FROM wp posts WHERE wp posts post status  publish  AND wp posts post type  post  GROUP BY wp posts post author            ORDER BY pdate DESC   When you don t specifies the table in ORDER BY  just the alias  they will order the result of the select

User · Answer

Try this one  Just get the list of latest post dates from each author  Thats it  SELECT wp posts   FROM wp posts WHERE wp posts post status  publish  AND wp posts post type  post  AND wp posts post date IN SELECT MAX wp posts post date  FROM wp posts GROUP BY wp posts post author

User · Answer

Sub queries may have a bad impact on performance when used with large datasets       Original query  SELECT wp posts   FROM   wp posts WHERE  wp posts post status    publish         AND wp posts post type    post  GROUP  BY wp posts post author ORDER  BY wp posts post date DESC     Modified query  SELECT p post status         p post type         Max p post date          p post author FROM   wp posts P WHERE  p post status    publish         AND p post type    post  GROUP  BY p post author ORDER  BY p post date     becasue i m using max in the select clause     max p post date  it is possible to avoid sub select queries and order by the max column after the group by

User · Answer

Just use the max function and group function      select max taskhistory id  as id from taskhistory             group by taskhistory taskid             order by taskhistory datum desc

User · Answer

Your solution makes use of an extension to GROUP BY clause that permits to group by some fields  in this case  just post author    GROUP BY wp posts post author   and select nonaggregated columns   SELECT wp posts     that are not listed in the group by clause  or that are not used in an aggregate function  MIN  MAX  COUNT  etc     Correct use of extension to GROUP BY clause  This is useful when all values of non-aggregated columns are equal for every row   For example  suppose you have a table GardensFlowers  name of the garden  flower that grows in the garden    INSERT INTO GardensFlowers VALUES   Central Park          Magnolia      Hyde Park             Tulip      Gardens By The Bay    Peony      Gardens By The Bay    Cherry Blossom      and you want to extract all the flowers that grows in a garden  where multiple flowers grow  Then you have to use a subquery  for example you could use this   SELECT GardensFlowers   FROM   GardensFlowers WHERE  name IN  SELECT   name                 FROM     GardensFlowers                 GROUP BY name                 HAVING   COUNT DISTINCT flower  gt 1     If you need to extract all the flowers that are the only flowers in the garder instead  you could just change the HAVING condition to HAVING COUNT DISTINCT flower  1  but MySql also allows you to use this   SELECT   GardensFlowers   FROM     GardensFlowers GROUP BY name HAVING   COUNT DISTINCT flower  1    no subquery  not standard SQL  but simpler   Incorrect use of extension to GROUP BY clause  But what happens if you SELECT non-aggregated columns that are non equal for every row  Which is the value that MySql chooses for that column   It looks like MySql always chooses the FIRST value it encounters   To make sure that the first value it encounters is exactly the value you want  you need to apply a GROUP BY to an ordered query  hence the need to use a subquery  You can t do it otherwise   Given the assumption that MySql always chooses the first row it encounters  you are correcly sorting the rows before the GROUP BY  But unfortunately  if you read the documentation carefully  you ll notice that this assumption is not true   When selecting non-aggregated columns that are not always the same  MySql is free to choose any value  so the resulting value that it actually shows is indeterminate   I see that this trick to get the first value of a non-aggregated column is used a lot  and it usually almost always works  I use it as well sometimes  at my own risk   But since it s not documented  you can t rely on this behaviour   This link  thanks ypercube   GROUP BY trick has been optimized away shows a situation in which the same query returns different results between MySql and MariaDB  probably because of a different optimization engine   So  if this trick works  it s just a matter of luck   The accepted answer on the other question  looks wrong to me   HAVING wp posts post date   MAX wp posts post date    wp posts post date is a non-aggregated column  and its value will be officially undetermined  but it will likely be the first post date encountered  But since the GROUP BY trick is applied to an unordered table  it is not sure which is the first post date encountered   It will probably returns posts that are the only posts of a single author  but even this is not always certain   A possible solution  I think that this could be a possible solution   SELECT wp posts   FROM   wp posts WHERE  id IN     SELECT max id    FROM wp posts   WHERE  post author  post date          SELECT   post author  max post date      FROM     wp posts     WHERE    wp posts post status  publish               AND wp posts post type  post      GROUP BY post author     AND wp posts post status  publish      AND wp posts post type  post    GROUP BY post author     On the inner query I m returning the maximum post date for every author  I m then taking into consideration the fact that the same author could theorically have two posts at the same time  so I m getting only the maximum ID  And then I m returning all rows that have those maximum IDs  It could be made faster using joins instead of IN clause    If you re sure that ID is only increasing  and if ID1  gt  ID2 also means that post date1  gt  post date2  then the query could be made much more simple  but I m not sure if this is the case

User · Answer

What you are going to read is rather hacky  so don t try this at home   In SQL in general the answer to your question is NO  but because of the relaxed mode of the GROUP BY  mentioned by  bluefeet   the answer is YES in MySQL   Suppose  you have a BTREE index on  post status  post type  post author  post date   How does the index look like under the hood    post status  publish   post type  post   post author  user A   post date  2012-12-01    post status  publish   post type  post   post author  user A   post date  2012-12-31    post status  publish   post type  post   post author  user B   post date  2012-10-01    post status  publish   post type  post   post author  user B   post date  2012-12-01    That is data is sorted by all those fields in ascending order   When you are doing a GROUP BY by default it sorts data by the grouping field  post author  in our case  post status  post type are required by the WHERE clause  and if there is a matching index  it takes data for each first record in ascending order  That is the query will fetch the following  the first post for each user     post status  publish   post type  post   post author  user A   post date  2012-12-01    post status  publish   post type  post   post author  user B   post date  2012-10-01    But GROUP BY in MySQL allows you to specify the order explicitly  And when you request post user in descending order  it will walk through our index in the opposite order  still taking the first record for each group which is actually last   That is      WHERE wp posts post status  publish  AND wp posts post type  post  GROUP BY wp posts post author DESC   will give us   post status  publish   post type  post   post author  user B   post date  2012-12-01    post status  publish   post type  post   post author  user A   post date  2012-12-31    Now  when you order the results of the grouping by post date  you get the data you wanted   SELECT wp posts   FROM wp posts WHERE wp posts post status  publish  AND wp posts post type  post  GROUP BY wp posts post author DESC ORDER BY wp posts post date DESC    NB   This is not what I would recommend for this particular query  In this case  I would use a slightly modified version of what  bluefeet suggests  But this technique might be very useful  Take a look at my answer here  Retrieving the last record in each group  Pitfalls  The disadvantages of the approach is that    the result of the query depends on the index  which is against the spirit of the SQL  indexes should only speed up queries    index does not know anything about its influence on the query  you or someone else in future might find the index too resource-consuming and change it somehow  breaking the query results  not only its performance  if you do not understand how the query works  most probably you ll forget the explanation in a month and the query will confuse you and your colleagues    The advantage is performance in hard cases  In this case  the performance of the query should be the same as in  bluefeet s query  because of amount of data involved in sorting  all data is loaded into a temporary table and then sorted  btw  his query requires the  post status  post type  post author  post date  index as well    What I would suggest   As I said  those queries make MySQL waste time sorting potentially huge amounts of data in a temporary table  In case you need paging  that is LIMIT is involved  most of the data is even thrown off  What I would do is minimize the amount of sorted data  that is sort and limit a minimum of data in the subquery and then join back to the whole table   SELECT    FROM wp posts INNER JOIN     SELECT max post date  post date  post author   FROM wp posts   WHERE post status  publish  AND post type  post    GROUP BY post author   ORDER BY post date DESC   -- LIMIT GOES HERE   p2 USING  post author  post date  WHERE post status  publish  AND post type  post     The same query using the approach described above   SELECT   FROM     SELECT post id   FROM wp posts   WHERE post status  publish  AND post type  post    GROUP BY post author DESC   ORDER BY post date DESC   -- LIMIT GOES HERE   as ids JOIN wp posts USING  post id     All those queries with their execution plans on SQLFiddle

[mysql] MySQL order by before group by

Examples related to mysql

Examples related to group-by

Examples related to sql-order-by