CODEWITHSUNDEEP
mysql
dotancohen
dotancohen

Reputation: 31471

How to query row with lowest value, and also to know the value of the highest value?

Consider these two queries:

SELECT *, 'b'    AS b    FROM someTable ORDER BY a ASC LIMIT 1;
SELECT *, MAX(a) AS maxA FROM someTable ORDER BY a ASC LIMIT 1;

The former query returns the row with the lowest value of a, as expected. The latter query returns the first row stored on disk (usually the row with the lowest value for primary key). How can I work around this? My intention is to get the full row of the column with the lowest a value (if there is more than one I only need one, it does not matter which), and additionally I do need the value of the highest age. In a perfect world I would run two queries, but due to the way that objects are serialised in this application I cannot do that without refactoring a lot of code that isn't mine. I actually don't mind if the MySQL engine itself must query twice, the important bit is that the output be returned in a single row. I cannot write a stored procedure for this query, unfortunately. And yes, the * operator is important, I cannot list the needed fields. And there are too many row to return them all!

Note that this question is superficially similar to a previous question, however the question asked there was ill-formed and ambiguous, therefore all the answers addressed the issue that was not my intention (however useful, I did learn much and I'm happy that it turned out that way). This question asks the intended question more clearly and so should attract different answers.

Upvotes: 1

Views: 113

Answers (2)

Imre L
Imre L

Reputation: 6249

use a subquery in select part: 

SELECT *, 'b' AS b,
       (SELECT MAX(a) FROM someTable) AS maxA
  FROM someTable ORDER BY a ASC LIMIT 1;

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220797

Why not just run this:

SELECT MIN(a) as minA, MAX(a) AS maxA FROM someTable

Unfortunately, MySQL doesn't know window functions. So if you really want to select * along with min/max values, I guess you'll have to resort to a JOIN:

SELECT * FROM 
(
  SELECT * FROM someTable ORDER BY a ASC LIMIT 1
) t1
CROSS JOIN
(
  SELECT MIN(a) as minA, MAX(a) AS maxA FROM someTable
) t2

Or to a subselect, as given in Imre L's answer

Upvotes: 4

Related Questions