How to select the title that has the highest value?

Question

So in a simple table like this one, how could I select the title which has the highest value?

|_title_||_value_|
|_title1_||_50652_|
|_title2_||_57465_|
|_title3_||_68565_|
|_title4_||_14645_|

Like in this case title3 has the highest value, so I'd like to select title3 with a SQL code, but I don't know how to

What I've tried:

SELECT title FROM table HAVING MAX(value);

I thought this is how I can do it, but it doesn't work. I'm completely new to SQL.

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Nick, Jan 25 '19 at 12:16

score 6 · Accepted Answer · edited Feb 08 '19 at 13:02

Use a subquery if you want to allow for multiple titles sharing the same maximum value:

select title from table where value = (select max(value) from table);

EDIT: As to your own query:

You aggregate all rows to a single one by asking for the MAX(value) without a GROUP BY clause. But then you select title. Which? You don't tell the DBMS and so the query is actually invalid SQL. MySQL, however, lets this slip and silently applies ANY_VALUE on title, which is not what you want. You want a particular one.

There is another flaw in your query: In HAVING MAX(value) you have no comparison (like in HAVING MAX(value) > 1000 for instance). But the DBMS expects an expression with a boolean result (true or false or null). Your expression should ideally raise an error, but MySQL simply converts the value to boolean with false = 0, true <> 0. 68565 is not zero, so the condition is true.

So you end up with a single row holding an arbitrarily picked title.

Great explanation. The only person to explain why OP's SQL wasn't working properly. — Mike Doe, Jan 25 '19 at 11:42

score 5 · Answer 2 · answered Jan 25 '19 at 11:21

5

use limit and order by using value in descending order

select * from table
order by value desc
limit 1

answered Jan 25 '19 at 11:21

Zaynul Abadin Tuhin

31,407
5
33
63

Yogesh Sharma · Answer 3 · 2019-01-25T11:24:17.233

5

You need order by clause with limit clause :

SELECT title
FROM table
ORDER BY val DESC
LIMIT 1;

This will return only 1 row, if you have same higher value then you will need to use subquery :

select t.*
from table t
where t.val = (select max(t1.val) from table t1)

edited Jan 25 '19 at 11:24

answered Jan 25 '19 at 11:21

Yogesh Sharma

49,870
5
26
52

score 3 · Answer 4 · answered Jan 25 '19 at 11:24

If using MySQL 8+ or later, we can use analytic functions:

WITH cte AS (
    SELECT title, value, ROW_NUMBER() OVER (ORDER BY value DESC) rn
    FROM yourTable
)

SELECT title, value
FROM cte
WHERE rn = 1;

The potential advantage of this approach is that if we want to also find all possible ties for first place, should they occur, we need only substitute ROW_NUMBER for RANK (or maybe DENSE_RANK), and we can get all ties for first.

score -1 · Answer 5 · answered Jan 25 '19 at 11:31

-1

SELECT title 
FROM t1
order by value desc
limit 1;

answered Jan 25 '19 at 11:31

Ankit Agrawal

2,426
1
13
27

How to select the title that has the highest value?

5 Answers5