CODEWITHSUNDEEP
sqlsql-server
Uz_IT
Uz_IT

Reputation: 27

SQL : Return joint most frequent values from a column

I have the following table named customerOrders.

ID    user    order
1      1        2
2      1        3
3      1        1
4      2        1
5      1        5
6      2        4
7      3        1
8      6        2
9      2        2
10     2        3

I want to return to users with most orders. Currently, I have the following QUERY:

SELECT user, COUNT(user) AS UsersWithMostOrders
FROM customerOrders
GROUP BY user
ORDER BY UsersWithMostOrders DESC;

This returns me all the values grouped by total orders like.

user     UsersWithMostOrders
1               4
2               4
3               1
6               1

I only want to return the users with most orders. In my case that would be user 1 and 2 since both of them have 4 orders. If I use TOP 1 or LIMIT, it will only return the first user. If I use TOP 2, it will only work in this scenario, it will return invalid data when top two users have different count of orders.

Required Result

user    UsersWithMostOrders
 1              4
 2              4

Upvotes: 1

Views: 71

Answers (4)

kiruba
kiruba

Reputation: 139

Below Oracle Query can help:

WITH test_table AS
(
SELECT user, COUNT(order) AS total_order , DENSE_RANK() OVER (ORDER BY 
total_order desc) AS rank_orders  FROM customerOrders
GROUP BY user 
)
select * from test_table where rank_orders = 1

Upvotes: 0

Thiyagu
Thiyagu

Reputation: 1330

You can use a CTE to attain this Req:

;WITH CTE AS(
SELECT [user], COUNT(user) AS UsersWithMostOrders
FROM @T
GROUP BY [user])

SELECT M.* from CTE M
INNER JOIN ( SELECT
MAX(UsersWithMostOrders) AS MaximumOrders FROM CTE) S ON 
M.UsersWithMostOrders=S.MaximumOrders

Upvotes: 0

Kurt Kline
Kurt Kline

Reputation: 2069

Option 1
Should work with most versions of SQL. 

select * 
from (
  select    *, 
          rank() over(order by numOrders desc) as rrank
  from (
    select `user`, count(*) as numOrders
    from customerOrders
    group by `user`
  ) summed 
) ranked 
where rrank = 1

Play around with the code here

Option 2
If your version of SQL allows window functions (with), here is a much more readable solution which does the same thing

with summed as (
    select `user`, count(*) as numOrders
    from customerOrders
    group by `user` 
),
ranked as (
    select *, 
           rank() over(order by numOrders desc) as rrank
    from summed 
)
select * 
from ranked 
where rrank = 1

Play around with the code here

Upvotes: 1

forpas
forpas

Reputation: 164099

You can use TOP 1 WITH TIES:

SELECT TOP 1 WITH TIES
  [user], COUNT(*) AS UsersWithMostOrders
FROM customerOrders
GROUP BY [user]
ORDER BY UsersWithMostOrders DESC;

See the demo.
Results:

> user | UsersWithMostOrders
> ---: | ------------------:
>    1 |                   4
>    2 |                   4

Upvotes: 2

Related Questions