Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql/mysql filter including only the max value

Tags:

sql

mysql

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

like image 544
skyline26 Avatar asked Sep 04 '12 05:09

skyline26


People also ask

How do you select a row with maximum value?

We used the MAX() function within a subquery to find the maximum value, and returned the whole row with the outer query.

Can we use Max in subquery?

To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).

How do I combine max and count in SQL?

To get one row with the highest count, you can use ORDER BY ct LIMIT 1 : SELECT c. yr, count(*) AS ct FROM actor a JOIN casting c ON c. actorid = a.id WHERE a.name = 'John Travolta' GROUP BY c.

How do I return a max value in SQL?

The MAX() function returns the largest value of the selected column.


1 Answers

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.

like image 137
Himanshu Jansari Avatar answered Oct 03 '22 11:10

Himanshu Jansari