sql/mysql filter including only the max value

Question

I have a resultset that is 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 rows that contains the highest "myvalue" available (in the previous example is 33), then:

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

IE the query should pick the highest "myvalue" available (IE 33) and it should remove the rows that have myvalue < 33

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

Hoping to have been clear...

thank you in advance

edit:

my current query is

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

now the highest myvalue can be 10, 20, 30, any number... in the final resultset 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 the...

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

...twice

thank you for your answers, is there a way to do this with one single select? — skyline26, Sep 04 '12 at 05:40
What do you exactly want? (want to repeat `myvalue` twice or once?) — Himanshu, Sep 04 '12 at 06:01
See [how to use `GROUP BY` clause in MySQL](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html) — Himanshu, Sep 04 '12 at 09:54
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/16232/discussion-between-hims056-and-topeerornottopeer) — Himanshu, Sep 04 '12 at 10:02

score 16 · Accepted Answer · edited Jan 18 '21 at 12:34

16

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

###See this SQLFiddle

Edit:

As per discussion with OP. OP wants to use alias in WHERE clause. But you can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.
Look at this answer.

edited Jan 18 '21 at 12:34

Community

1
1

answered Sep 04 '12 at 05:38

Himanshu

31,810
31
111
133

John Woo · Answer 2 · 2012-09-04T05:40:13.473

5

try this,

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

SQLFiddle Demo

thanks to hims056 for the DDL

edited Sep 04 '12 at 05:40

answered Sep 04 '12 at 05:30

John Woo

258,903
69
498
492

1

@toPeerOrNotToPeer no. you should calculate the max value separately. – John Woo Sep 04 '12 at 05:41
ouch!, myvalue is, actually, a relevance score in a fulltext search, that i want not to repeat twice... maybe it is possible using some custom function? btw thanks you all, +1 to every1 – skyline26 Sep 04 '12 at 05:44

score 3 · Answer 3 · answered Sep 04 '12 at 05:31

3

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

(if you use the commented code, will get the maximums per name :) )

answered Sep 04 '12 at 05:31

Florin Ghita

17,525
6
57
76

score 2 · Answer 4 · answered Jul 25 '13 at 10:48

2

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

answered Jul 25 '13 at 10:48

Vaci

29
1

score 1 · Answer 5 · answered Sep 04 '12 at 05:34

1

another way is :

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

answered Sep 04 '12 at 05:34

Grisha Weintraub

7,803
1
25
45

sql/mysql filter including only the max value

5 Answers5

SQLFiddle Demo

Linked