CODEWITHSUNDEEP
oracle-databasegroup-by
Baba Fooka
Baba Fooka

Reputation: 47

Max number of counts in a tparticular hour

I have a table called Orders, i want to get maximum number of orders for each day with respect to hours with following query

SELECT 
   trunc(created,'HH') as dated, 
   count(*) as Counts
 FROM 
   orders 
 WHERE 
   created > trunc(SYSDATE -2) 
 group by trunc(created,'HH') ORDER BY counts DESC

this gets the result of all hours, I want only max hour of a day e.g. Image

This result looks good but now i want only rows with max number of count for a day e.g.

for 12/23/2019 max number of counts is 90 for "12/23/2019 4:00:00 PM",
for 12/22/2019 max number of counts is 25 for "12/22/2019 3:00:00 PM"

required dataset 

1   12/23/2019 4:00:00 PM   90
2   12/24/2019 12:00:00 PM  76
3   12/22/2019 1:00:00 PM   25

Upvotes: 1

Views: 45

Answers (3)

Popeye
Popeye

Reputation: 35910

You can use the analytical function ROW_NUMBER as following to get the desired result:

SELECT DATED, COUNTS
FROM (
        SELECT
            TRUNC(CREATED, 'HH') AS DATED,
            COUNT(*) AS COUNTS,
            ROW_NUMBER() OVER(
                PARTITION BY TRUNC(CREATED)
                ORDER BY COUNT(*) DESC NULLS LAST
            ) AS RN
        FROM ORDERS
        WHERE CREATED > TRUNC(SYSDATE - 2)
        GROUP BY TRUNC(CREATED, 'HH'), TRUNC(CREATED)
    )
WHERE RN = 1

Cheers!!

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21075

Use ROW_NUMBER analytic function over your original query and filter the rows with number 1.

You need to partition on the day, i.e. TRUNC(dated) to get the correct result

with ord1 as (
SELECT 
   trunc(created,'HH') as dated, 
   count(*) as Counts
 FROM 
   orders 
 WHERE 
   created > trunc(SYSDATE -2) 
 group by trunc(created,'HH')
),
ord2 as (
select dated, Counts,
row_number() over (partition by trunc(dated) order by Counts desc) as rn
from ord1)
select dated, Counts
from ord2
where rn = 1

The advantage of using the ROW_NUMBER is that it correct handels ties, i.e. cases where there are more hour in a day with the same maximal count. The query shows only one record and you can controll with the order by e.g. to show the first / last hour.

Upvotes: 0

davidm
davidm

Reputation: 1760

This could be the solution and in my opinion is the most trivial.

Use the WITH clause to make a sub query then search for the greatest value in the data set on a specific date.

WITH ORD AS (
        SELECT 
            trunc(created,'HH') as dated, 
            count(*) as Counts
        FROM 
            orders 
        WHERE 
            created > trunc(SYSDATE-2) 
        group by trunc(created,'HH')
    )

SELECT * 
  FROM ORD ord 
WHERE NOT EXISTS (
     SELECT 'X' 
       FROM ORD ord1 
     WHERE trunc(ord1.dated) = trunc(ord.dated) AND ord1.Counts > ord.Counts
     )

Upvotes: 1

Related Questions