use orderby and group by together

Question

if I use order by and group by like this below

select * from (SELECT * FROM `entry` ORDER BY jc_epoc DESC)as b GROUP BY type

does this query always give me the biggest timeStamp in each type? if not , what is the right query?

Answer 1

No, It shouldn't but apparently it does work more often than not. The real problem with your query is that theoritically it violates 1NF. Assuming that mysql always selects the first row in such a case, you design the query but actually it may differ and is not reliable as is suggested by the documentation. 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.

The question requires more clarity as to how many rows per type is required say for multiple max value instances per type are present. But I get a vague idea as to what you are trying - "You want to get the max value for each type and along with it all the other column values for that row"

To make a query that is determinate I would do - If multiple rows per type is fine

SELECT *
FROM entry e1
WHERE (e1.type, e1.jc_epoc) IN
(    SELECT e.type, MAX(e.jc_epoc)
     FROM entry e
     GROUP BY e.type
)

If multiple rows per type is not fine, you'll have to use a key value to get single value

SELECT e2.* 
FROM entry e2 
LEFT OUTER JOIN entry e3 
ON e3.jc_epoc >= e2.jc_epoc
AND e3.type = e2.type
AND e3.id > e2.id
WHERE e3.id IS NULL

Note :- Queries above may have more optimizations possible. For more clear/concise requirements queries may have optimized versions Ex- for only MAX(jc_epoc) per type required and no other column values

SELECT e.type, MAX(e.jc_epoc)
FROM entry e
GROUP BY e.type

Feel free to SQLFiddle it.