Question 20326 in Mysql Question posted in
The official MySQL documentation can be found here.

How to find top rows based on max coulmn and group by another coulmn? – Mysql

I have this table (simplified version):

id state place temp
1 AA PLC1 10
2 AA PLC2 20
3 AA PLC3 25
4 EE PLC4 11
5 EE PLC5 15
6 EE PLC6 19
. .. …. ..

How to find top 2 hottest places (based on temp column) from X states ?
The states will be provided during query time, i.e there may be 2 or 5 states which is not known in advance.

Tags:

4

Answers


  1. 0

    How to find top 2 hottest places (based on temp column) from X states
    ? The states will be provided during query time, i.e there may be 2 or
    5 states which is not known in advance.

    The following query shall give you the desired result

    SELECT t1.state, t1.place, t1.temp
FROM table_name t1
WHERE t1.state IN ('state1', 'state2', ...)
  AND (
    SELECT COUNT(*)
    FROM table_name t2
    WHERE t2.state = t1.state AND t2.temp >= t1.temp
  ) <= 2
ORDER BY t1.state, t1.temp DESC;

    Here is the SQLFIDDLE using your sample data.

    The query :

    SELECT t1.state, t1.place, t1.temp
FROM table_name t1
WHERE t1.state IN ('AA', 'EE')
  AND (
    SELECT COUNT(*)
    FROM table_name t2
    WHERE t2.state = t1.state AND t2.temp >= t1.temp
  ) <= 2
ORDER BY t1.state, t1.temp DESC;

    Returns correct output as :

    state   place   temp
AA      PLC3    25
AA      PLC2    20
EE      PLC6    19
EE      PLC5    15

    The query can be also written using ROW_NUMBER with PARTITION BY clause as in this demo :

    SELECT t.state, t.place, t.temp
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY state ORDER BY temp DESC) as rn
  FROM table_name
  WHERE state IN ('AA', 'EE')
) t
WHERE t.rn <= 2
ORDER BY t.state, t.temp DESC;
    Login or Signup to reply.
  2. 0

    You can use ROW_NUMBER with partition by clause:

    select
    *
from
    (
        select
            *,
            ROW_NUMBER() OVER (
                partition by
                    state
                order by
                    temp desc
            )
        from
            table1
    )
where
    rn <= 2
    Login or Signup to reply.
  3. 0

    If you have Mysql 8, check out ranking functions. For example you can use dense_rank() to also account for ties.

    WITH ranked_temps as (
  select id,state,place, temp, 
  dense_rank() over ( 
     partition by state 
     order by temp desc
  ) dr
  from table_name
)
select id,state,place,temp from ranked_temps where dr <=2

    fiddle

    Login or Signup to reply.
  4. 0

    You can do it using rank() or dense_rank, this will return more than 2 top places if there are places with the same temp,

    If this what you want then try this :

    WITH cte as (
  select *, 
  rank() over ( partition by state  order by temp desc ) rn
  from mytable
)
select id, state, place, temp 
from cte where rn <=2

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search