MySQL "Group By" and "Order By"

MySQL Group By and Order By

A simple solution is to wrap the query into a subselect with the ORDER statement first and applying the GROUP BY later:

SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

This is similar to using the join but looks much nicer.

Using non-aggregate columns in a SELECT with a GROUP BY clause is non-standard. MySQL will generally return the values of the first row it finds and discard the rest. Any ORDER BY clauses will only apply to the returned column value, not to the discarded ones.

IMPORTANT UPDATE
Selecting non-aggregate columns used to work in practice but should not be relied upon. Per the MySQL documentation "this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

As of 5.7.5 ONLY_FULL_GROUP_BY is enabled by default so non-aggregate columns cause query errors (ER_WRONG_FIELD_WITH_GROUP)

As @mikep points out below the solution is to use ANY_VALUE() from 5.7 and above

See
http://www.cafewebmaster.com/mysql-order-sort-group
https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

Using ORDER BY and GROUP BY together

One way to do this that correctly uses group by:

select l.* 
from table l
inner join (
  select 
    m_id, max(timestamp) as latest 
  from table 
  group by m_id
) r
  on l.timestamp = r.latest and l.m_id = r.m_id
order by timestamp desc

How this works:

• selects the latest timestamp for each distinct m_id in the subquery
• only selects rows from table that match a row from the subquery (this operation -- where a join is performed, but no columns are selected from the second table, it's just used as a filter -- is known as a "semijoin" in case you were curious)
• orders the rows

MySQL Using ORDER BY and GROUP BY together

First of all just to point out, you should train yourself to not use group by if you are not intending to include all non-aggregated columns in the grouping. Example:

SELECT col1,col2 FROM table GROUP BY col1 -- BAD
SELECT col1,col2 FROM table GROUP BY col1,col2 -- GOOD
SELECT col1,MAX(col2) FROM table GROUP BY col1 -- GOOD

This is because of the obvious problem that you get an arbitrary and unpredictable value in columns that are not unique within the group which is the source of all your problems.

Having said that you should do this:

SELECT item_category, MAX(item_id) 
FROM items 
WHERE item_category IN ('2','4','12','13','14','19') GROUP BY `item_category`

Note that there is no need to limit since you can only have 6 groups and order by doesn't do anything anymore.

If you want to get the corresponding item rows you can do the following:

SELECT * 
FROM items 
WHERE item_id IN ( 
     SELECT MAX(item_id) 
     FROM items 
     WHERE item_category IN ('2','4','12','13','14','19') 
     GROUP BY item_category
);

The following might also work:

SELECT * 
FROM items i JOIN (
       SELECT i2.item_category, MAX(i2.item_id) as max_item_id 
       FROM items i2
       WHERE i2.item_category IN ('2','4','12','13','14','19') GROUP BY 
       i2.`item_category`
) max_ids ON i.item_id=max_ids.max_item_id;

MYSQL - Group by and Order By

Try this:

SELECT t1.ID, t1.Field1, t1.Field2, t1.Field3
FROM mytable AS t1
JOIN (
  SELECT Field1, Field2, MAX(Field3) AS min_field3,
         COUNT(CASE WHEN Field3 = 'Cheap' THEN 1 END) AS cnt_cheap
  FROM mytable
  GROUP BY Field1, Field2
) AS t2 ON t1.Field1 = t2.Field1 AND 
           t1.Field2 = t2.Field2 AND
           t1.Field3 = IF(t2.cnt_cheap = 1, 'Cheap', min_field3)
ORDER BY FIELD(Field3, 'Cheap') DESC 

The above query picks the 'Cheap' record from a Field1, Field2 group, if such a record exists, otherwise it picks the record having the maximum Field3 value.

MYSQL - Order By Id In DESC Order, Group By X

You have misunderstood how GROUP BY works in SQL, due to a feature of MySQL. In standard SQL every non aggregate column in the SELECT statement MUST be in the GROUP BY clause (there is an exception for columns whose values are 100% dependent on a column already in the GROUP BY clause, although few flavours of SQL support this exemption).

MySQL does not enforce this by default, but which rows values are used for those columns is not defined. While you might get the one you want, you also might not. And even if you do there is a chance that it will change in the future.

The ordering is independent of the GROUP BY normally, although if you do not specify an ORDER clause then the results will be ordered based on what was required to perform the GROUPing (ie, if it helps to order the rows in one order to do the GROUP BY then MySQL will not bother to reorder the records afterwards unless you specifically tell it to with an ORDER BY clause).

So with your current data, grouping by ads_post_id the value of id that is returned could be 22, 23, 24, 104, 250, 253 or 767. Which one MySQL choses to use is not defined.

With your current data fixing this is trivial as you can just get the MAX id:-

SELECT ads_post_id, MAX(id) 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

MAX will return 1 row for each GROUPed value.

The normal problem is that people want another column for that row. For example say that each of the rows in your sample data also had an IP address, and you wanted the one that equated to the highest id for the ads_post_id:-

id   | ads_post_id         ip_address
---------------------------------------------------------------------------
22   | 983314845117571     192.168.0.0
23   | 983314845117571     192.168.0.5
24   | 983314845117571     192.168.0.7    
104  | 983314845117571     192.168.0.0
250  | 983314845117571     192.168.0.4
253  | 983314845117571     192.168.0.6
767  | 983314845117571     192.168.0.1     
---------------------------------------------------------------------------

In this case you cannot just use MAX. For example if you tried:-

SELECT ads_post_id, MAX(id), MAX(ip_address) 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

You would get the following data returned

id   | ads_post_id         ip_address
---------------------------------------------------------------------------
767  | 983314845117571     192.168.0.7     
---------------------------------------------------------------------------

If you tried the following in most flavours of SQL you would get an error. In MySQL with the default settings you would get a result, but which IP address is returned is not defined (and in effect random).

SELECT ads_post_id, MAX(id), ip_address 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

The solutions to this are either to get the max id for each ads_post_id in a sub query and then joining that back to the table to get the rest of the values:-

SELECT a.ads_post_id,
        a.id,
        a.ip_address
FROM fb_ads a
INNER JOIN
(
    SELECT ads_post_id, MAX(id) AS max_id 
    FROM fb_ads 
    GROUP BY ads_post_id 
) sub0
ON a.ads_post_id = sub0.ads_post_id
AND a.id = sub0.max_id

An alternative is to (ab)use the GROUP_CONCAT aggregate function. GROUP_CONCAT will bring back all the values concatenated together into 1 field, each separated by a , (by default). You can add an ORDER BY clause to force the order they are concatenated into. The you can use SUBSTRING_INDEX to return everything up to the first comma.

This can be useful for simple data, but becomes problematic with text data or fields that max be NULL.

SELECT a.ads_post_id,
        SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(ip_address ORDER BY id DESC), ',', 1)
FROM fb_ads 
GROUP BY ads_post_id 

MySQL select GROUP BY order

This may work (but not guaranteed):

SELECT * 
FROM
  ( SELECT *
    FROM tbl_messages 
    WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' 
    ORDER BY date_sent DESC
  ) tmp
GROUP BY from_user_id 
ORDER BY date_sent DESC

---

This should work:

SELECT t.* 
FROM 
    tbl_messages AS t
  JOIN
    ( SELECT from_user_id 
           , MAX(date_sent) AS max_date_sent
      FROM tbl_messages 
      WHERE to_user_id = '$user_id' OR from_user_id = '$user_id' 
      GROUP BY from_user_id 
    ) AS tg
    ON  (tg.from_user_id, tg.max_date_sent) = (t.from_user_id, t.date_sent)
ORDER BY t.date_sent DESC

Mysql GROUP BY ...but I want the oldest one

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY COALESCE(category, id)
                                 ORDER BY date DESC) rn
    FROM test
)

SELECT name, date, category, id
FROM cte
WHERE rn = 1;

select top 5 group by and order by

Maybe you want something like this?

select top 5 CITY, QNT, EXP, RATE 
from (
    select *, row_number() over (partition by CITY order by RATE desc) AS RN
    from (
        select CITY, QNT, EXP, (QNT-EXP)*100/EXP as RATE
        from tbl_city

    ) X
) Y
where RN = 1
order by RATE desc

I didn't test this, but it should take first the row for the city with biggest rate, and then take top 5 rows so that that the same city is not duplicated

---

Related Topics