CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-groupimpala
Laxmikant sahu
Laxmikant sahu

Reputation: 73

how to get the max count and respective hour_id for a date?

following is the source data from which I am trying get the maximum count and respective hour_id and id value. which should be only one row for a date , right now there are multiple values coming for a particular date . 

   id            hour_id    count   date
    621f50772a36e7  23      14474   20141202
    621f50157c2973  0       7190    20141203
    621f5077582f54  7       5043    20141225
    621f505247c107  11      5023    20141224
    621f50251c8b33  10      4943    20141224
    621f5076c9327b  18      4901    20150113
    621f50044c300e  10      4868    20141212
    621f500e10fa5d  12      4858    20141224
    621f505242ec27  9       4843    20141224
    621f505263bc56  14      4716    20141231
    621f50774a456c  19      4712    20141206
    621f5077414404  19      4674    20141123
    621f5077362f46  19      4666    20141224
    621f505246ea97  10      4662    20141225
    621f50522c6bf5  13      4626    20141226
    621f5076c87607  13      4586    20141231
    621f5052297007  17      4574    20141224

I have tried this :

select max(count) cc
,partition_date
,location_id
,hour_id 
from 
(
     select 
     l.location_id
     , substr(x.evt_timestamp,9,2) as hour_id
     , count(1) as count 
     ,partition_date
     from prismeventdetails x
     join l_cellsite_location l
     on x.evt_location = l.location_id
     where x.evt_type = '100'           
     group by l.location_id
     ,hour_id 
     ,partition_date
     order by  cc desc  limit 500
) c 
group by partition_date,location_id,hour_id

but not able to get the desired result. getting multiple rows for one date .because I have included hour_id in group by
. I want hour_id and id values where count is max for that date . need help , early response will be appreciated .

Upvotes: 0

Views: 446

Answers (3)

Isaac
Isaac

Reputation: 16392

The following, a join on the GROUPed and MAXed table with the original table, should get you want you want.

WITH maxed AS
(
  SELECT max(count) AS max_count, date_
  FROM tests.so_30595512
  GROUP BY date_
)

SELECT maxed.date_, maxed.max_count, t2.hour, t2.id
FROM maxed
JOIN tests.so_30595512 AS t2
ON maxed.date_ = t2.date_
AND maxed.max_count = t2.count;

This is a query on the table you outlined above, but this same approach will translate easily to whatever joined table you're working with in your example query.

Upvotes: 1

Sharvari
Sharvari

Reputation: 41

try this one:

select [count] cc
,partition_date
,location_id
,hour_id 
from 
(
     select 
     l.location_id
     , substr(x.evt_timestamp,9,2) as hour_id
     , count(1) as [count] 
     ,partition_date
     from prismeventdetails x
     join l_cellsite_location l
     on x.evt_location = l.location_id
     where x.evt_type = '100'           
     group by l.location_id
     ,hour_id 
     ,partition_date
,ROW_NUMBER() over (ORDER BY [count] DESC) AS Number
     order by  cc desc  limit 500
) c 
WHERE Number = 1
group by partition_date,location_id,hour_id

Upvotes: 0

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

Try to move MAX to subquery in following:

select counts cc
,partition_date
,location_id
,hour_id 
from 
(
     select 
     l.location_id
     , substr(x.evt_timestamp,9,2) as hour_id
     , max(count(1)) as counts 
     ,partition_date
     from prismeventdetails x
     join l_cellsite_location l
     on x.evt_location = l.location_id
     where x.evt_type = '100'           
     group by partition_date, location_id, hour_id
     order by  cc desc  limit 500
)c

Upvotes: 0

Related Questions