开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜