ORDER BY priority over GROUP BY in MySQL without subquery

Question

I have the following query that does what I want, but I suspect that this can be done without a subquery:

  SELECT * 
    FROM (SELECT * 
            FROM 'versions' 
        ORDER BY 'ID' DESC) AS X 
GROUP BY 'program'

I need a program group, but returning results for objects in versions with the highest "ID" value.

In my past experience, such a query should work in MySQL, but for some reason this is not the case:

  SELECT * 
    FROM 'versions' 
GROUP BY 'program' 
ORDER BY MAX('ID') DESC

What I want to want is to do MySQL first ORDER BY and then GROUP BY, but he insists on doing GROUP BY first and then ORDER BY first. those. it sorts the grouping results instead of grouping the ordering results.

Of course write

impossible

SELECT * FROM 'versions' ORDER BY 'ID' DESC GROUP BY 'program'

Thank.

Answer 1

, ( , id) . .

select v.*
from
(
    select program, MAX(id) id
    from versions
    group by program
) m
inner join versions v on m.program=v.program and m.id=v.id

Answer 2

SELECT  v.*
FROM    (
        SELECT  DISTINCT program
        FROM    versions
        ) vd
JOIN    versions v
ON      v.id = 
        (
        SELECT  vi.id
        FROM    versions vi
        WHERE   vi.program = vd.program
        ORDER BY
                vi.program DESC, vi.id DESC
        LIMIT 1
        )

(program, id), .

:

SELECT * FROM 'versions' GROUP BY 'program' ORDER BY MAX('ID') DESC

SQL, MySQL.

MySQL GROUP BY.

Answer 3

ORDER BY GROUP BY. SELECT:

• , FROM
• FROM
• WHERE .
• GROUP BY, - SUM(), MAX(), AVG() .. - . : GROUP BY, , , , .
• HAVING.
• ORDER BY.

The only columns allowed in the SELECT result set with the GROUP BY clause are, of course,

• Columns specified in the GROUP BY clause
• Aggregate functions (e.g. MAX())
• alphabetic / constant
• expressions derived from any of the above.

Only broken SQL implementations allow things like select xxx,yyy,a,b,c FROM foo GROUP BY xxx,yyy- references to colulmsn a, b and c are meaningless / undefined, given that individual groups were collapsed into one line,