CODEWITHSUNDEEP
mysql
kumar
kumar

Reputation: 159

how to select max value with corresponding id in mysql

SELECT 
    id
  , insId
  , MAX(avgRating) as avgRating 
FROM reviews 
WHERE status='1' 
GROUP BY insId

this query returns all reviews grouped by insId but id is not corresponding to this.

Upvotes: 0

Views: 429

Answers (3)

king zecole
king zecole

Reputation: 59

You can try this out:

SELECT id, 
insId, 
MAX(avgRating) as avgRating 
FROM reviews 
WHERE status='1' 
GROUP BY id, insId

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

You need to use a self INNER JOIN to get the results you need.

SELECT
   reviews.id
 , reviews_max.insId
 , reviews_max.maxAvgRating
FROM (

  SELECT 
     insId
   , MAX(avgRating) AS maxAvgRating
  FROM
   reviews
  WHERE
   status = '1'
  GROUP BY
   insId
) AS reviews_max
INNER JOIN 
 reviews
ON
   reviews.insId = reviews_max.insId 
 AND
   reviews.avgRating = reviews_max.maxAvgRating

Upvotes: 1

Andrei
Andrei

Reputation: 5637

Try this:

SELECT 
    id
  , insId
  , MAX(avgRating) as avgRating 
FROM reviews 
WHERE status='1' 
GROUP BY id

Upvotes: 0

Related Questions