CODEWITHSUNDEEP
mysql
Siddharth Thevaril
Siddharth Thevaril

Reputation: 3798

Get the entire row/rows of a cell with the maximum value

I have the following table:

enter image description here

I need to find the row with the maximum product_count. This is the SQL that I have tried but it doesn't give the correct result:

SELECT product_id, MAX(product_count)
GROUP BY product_id

EDIT:

SELECT product_id, MAX(product_count) as max_count
FROM
(
    SELECT product_id, COUNT(product_id) as product_count
    FROM wp_stvp_voting_data
    GROUP BY product_id
) as T1
GROUP BY product_id

Upvotes: 0

Views: 46

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522181

One simple option is to use LIMIT:

SELECT product_id, COUNT(product_id) AS product_count
FROM wp_stvp_voting_data
GROUP BY product_id
ORDER BY COUNT(product_id) DESC
LIMIT 1;

If you needed to cater to the possibility that there might be more than one record tied for the maximum product count, and you wanted to return all ties, then we could try using a subquery:

SELECT product_id, COUNT(product_id) AS product_count
FROM wp_stvp_voting_data
GROUP BY product_id
HAVING COUNT(product_id) = (SELECT MAX(cnt) FROM (
                               SELECT COUNT(*) AS cnt
                               FROM yourTable
                               GROUP BY product_id
                           ) t)

If you are using MySQL 8+, then we would be able to take advantage of ROW_NUMBER:

SELECT product_id, product_count
FROM
(
    SELECT product_id, COUNT(product_id) AS product_count,
        ROW_NUMBER() OVER (ORDER BY COUNT(product_id) DESC) rn
    FROM yourTable
    GROUP BY product_id
) t
WHERE rn = 1;

Replace ROW_NUMBER with RANK if you want all ties for first place.

Upvotes: 3

Related Questions