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
SELECT * FROM t WHERE myValue IN (SELECT max(myValue) From t);
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.