Extract only rows with highest values-CodePudding

I am relatively new to SQL and I am trying to extract rows where they have the highest values.

For example, the table look like this:

user_id  fruits
1        apple
1        orange
2        apple
1        pear

I would like to extract the data such that it would look like this:

user_id  fruits
1        3

If user_id 2 has 3 fruits, it should display:

user_id  fruits
1        3
2        3

I can only manage to get the if I use LIMIT = 1 by DESC order, but that is not the right way to do it. Otherwise I am getting only:

user_id  fruits
1        3
2        1

Not sure where to store the max value to put in the where clause. Appreciate any help, thank you

CodePudding user response：

Use RANK():

WITH cte AS (
    SELECT user_id, COUNT(*) AS cnt, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
    FROM yourTable
    GROUP BY user_id
)

SELECT user_id, cnt AS fruits
FROM cte
WHERE rnk = 1;

CodePudding user response：

Here's one answer (with sample data):

CREATE TABLE something  (user_id INT NOT NULL, fruits VARCHAR(10) NOT NULL, PRIMARY KEY (user_id, fruits));
INSERT INTO something VALUES (1, 'apple');
INSERT INTO something VALUES (1, 'orange');
INSERT INTO something VALUES (2, 'apple');
INSERT INTO something VALUES (1, 'pear');
INSERT INTO something VALUES (2, 'orange');
INSERT INTO something VALUES (2, 'pear');

SELECT user_id, COUNT(*) AS cnt
FROM something
GROUP BY user_id
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM something GROUP BY user_id);