How to select the title that has the highest value?

  • A+
Category:Languages

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

It's probably not that hard, but I'm completely new to SQL

I'd really appreciate if someone could help me out with this

 


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 comparision (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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: