CODEWITHSUNDEEP
mysql
user4386053
user4386053

Reputation:

MySQL - COUNT() a MAX()

Trying to update a column with the MAX() in another column, AND COUNT() the number of times MAX() occurs. I've searched around and it seems like I have the right syntax... Not sure what the problem is.

Would appreciate any advice.

UPDATE `working` SET `NO_TOP_RATING` = 

(SELECT MAX(`IRECCD`), COUNT(`ID`) FROM `working_2` 
 WHERE 
 `working_2`.`ANNDATS_CONVERTED` >= DATE_SUB(`working`.`ANNDATS_CONVERTED`,INTERVAL 1 YEAR)
 AND 
 `working_2`.`ID` != `working`.`ID`
 AND 
 `working_2`.`ID` != `working`.`ID`
 AND
 `working_2`.`ESTIMID` = `working`.`ESTIMID` 
)
WHERE `working`.`ANALYST` != ''

What is the proper way to count() the occurrences of returned max() ?

Thank you.

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

I think it is simpler to use order by and limit:

UPDATE `working` w
    SET `NO_TOP_RATING` = (SELECT COUNT(*)
                           FROM working_2 w2
                           WHERE w2.`ANNDATS_CONVERTED` >= DATE_SUB(w.`ANNDATS_CONVERTED`, INTERVAL 1 YEAR) AND
                                 w2.`ID <> w.`ID`
                                 w2.`ESTIMID` = w.`ESTIMID`
                           GROUP BY IRECCD
                           ORDER BY IRECCD DESC
                           LIMIT 1
                          )

    WHERE w.`ANALYST` <> '';

Upvotes: 1

Iłya Bursov
Iłya Bursov

Reputation: 24229

you're selecting 2 columns, and trying to insert them into one - this will not work

I suppose you can solve this via:

UPDATE `working` SET `NO_TOP_RATING` = 
(SELECT COUNT(*) FROM `working_2`
 WHERE `IRECCD` = (
  select MAX(`IRECCD`) FROM `working_2`
  WHERE 
  `working_2`.`ANNDATS_CONVERTED` >= DATE_SUB(`working`.`ANNDATS_CONVERTED`,INTERVAL 1 YEAR)
  AND 
  `working_2`.`ID` != `working`.`ID`
  AND 
  `working_2`.`ID` != `working`.`ID`
  AND
  `working_2`.`ESTIMID` = `working`.`ESTIMID`
 )
)
WHERE `working`.`ANALYST` != ''

Upvotes: 0

Related Questions