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.