MAX() in WHERE clause
The following sql, with the MAX aggregrate function in the WHERE clause, does not work:
SELECT
ID,
title,
relevance
FROM
myTable
WHERE
(relevance <= MAX(relevance)/2)
Can anyone point me 开发者_运维技巧in the right direction? myTable is a temporary table, so cannot be referenced again using a sub-query. Thanks!
SET @MaxRel=(SELECT MAX(relevance)/2 FROM myTable);
SELECT ID,title,relevance FROM myTable WHERE (relevance <= @MaxRel);
May this help
try using sub query
SELECT
ID,
title,
relevance
FROM
myTable
WHERE
(relevance <= (SELECT MAX(relevance)/2 FROM myTable))
another way using having like
SELECT
ID,
title,
relevance
FROM
myTable
GROUP BY ID
HAVING
(relevance <= MAX(relevance)/2)
You need to use a subquery
SELECT
ID,
title,
relevance
FROM
myTable
WHERE
(relevance <= (SELECT MAX(relevance) FROM myTable) /2)
You should be able to do something like this. (Untested, so please let me know if this doesn't work as expected.)
SELECT * FROM
(
SELECT
ID,
title,
relevance,
IF( relevance > @max, @max := relevance, @max := @max ) AS max_relevance
FROM
mytable,
(SELECT @max:=0) m
) Q
WHERE relevance <= max_relevance / 2
;
Common MySQL Queries for some good code examples. Look for Within-group quotas (Top N per group) for an example of how to use variables in a query as shown above.
the method to use a variable which is mentioned by naresh is a good way to query the database, as it creates an ease in understanding the queries.
SELECT
ID,
title,
relevance
FROM
myTable inner join (SELECT MAX(relevance) as maxRel FROM myTable) A
WHERE
(relevance <= (A.maxRel / 2))
Should still give you back the same number of rows as before, as "A" has only one row.
精彩评论