Best practice for doing select by max and column to return one record-CodePudding

I just learned that in my environment that full_group_by mode has been disabled. For example, if I wanted to get a column (name) and a max (id) on another column and return only that one record I would have done something like

SELECT max(id), name
FROM TABLE;

However, this only works if only_full_group_by is disabled. The alternative to this and I think most likely the better way and accepted practice to write this would be,

SELECT id, name
FROM TABLE
WHERE id = (SELECT max(id) from TABLE);

What is the correct and best practice here? I like the first perhaps because that is how I have been doing it forever and it's less code. The second way does seem to read better and is more clear in what it will return, but seems maybe slower since I am doing another SELECT statement in the WHERE.

CodePudding user response：

Your first query is invalid SQL, going by the ANSI standard, and should be avoided. If you only expect a single record having the maximum id value, or, if there are ties and you don't care which single record is returned, then you may use a LIMIT query:

SELECT id, name
FROM yourTable
ORDER BY id DESC
LIMIT 1;

Otherwise, if you need all ties, then use your second version:

SELECT id, name
FROM yourTable
WHERE id = (SELECT MAX(id) FROM yourTable);

Note that as of MySQL 8 , we can also use the RANK() analytic function here to get back all ties:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY id DESC) rnk
    FROM yourTable
)

SELECT id, name
FROM cte
WHERE rnk = 1;