Sql / mysql, including only the maximum value

Question

I have a result set that looks like this:

ID | name | myvalue 1 | A1 | 22 2 | A2 | 22 3 | A3 | 21 4 | A4 | 33 5 | A5 | 33 6 | A6 | 10 7 | A7 | 10 8 | A8 | 10 9 | A9 | 5 

what I want is to include only lines that contain the highest available "myvalue" (in the previous example - 33), and then:

 ID | name | myvalue 4 | A4 | 33 5 | A5 | 33 

The IE request should select the highest available "myvalue" (IE 33), and it should remove the lines that have myvalue <33

 SELECT ..... WHERE myvalue = THE_HIGHEST_OF(myvalue) 

In the hope that it was clear ...

early

---

edit:

my current request

 SELECT *, (very long code that returns a integer as relevance score) AS myvalue FROM mytable HAVING myvalue = ????? ORDER BY myvalue DESC 

now the highest value of myvalue can be 10, 20, 30, any number ... in the final result set I want to include only the rows that have the highest possible relevance score

ive tried using GROUP BY, but I always need to repeat ...

  (very long code that returns a integer as relevance score) AS myvalue 

... twice

Answer 1

 SELECT * FROM t WHERE myValue IN (SELECT max(myValue) From t); 

See this SQLFiddle

Edit:

By discussion with OP. OP wants to use an alias in WHERE . But you can only use column aliases in GROUP BY , ORDER BY or HAVING .
Check out this answer.

Answer 2

try it,

 SELECT * FROM tableName WHERE myValue = (SELECT max(myValue) From tableName) 

SQLFiddle Demo

thanks to hims056 for DDL

Answer 3

 select * from mytable a where my_value = (select max(myvalue) from my_table b --where b.name = a.name ) 

(if you use commented code, get maximum values ​​for the name :))

Answer 4

another way:

 select * from myTable m1 where not exists (select 1 from myTable where myValue > m1.myValue) 

Answer 5

 SELECT *, (very long code that returns a integer as relevance score) AS myvalue FROM mytable HAVING myvalue = MAX(myvalue)