CODEWITHSUNDEEP
sqloracle-database
Alexey
Alexey

Reputation: 41

SQL - Finding an hour of maximum activity

I was trying it different ways, but still don't know how to get this result(

enter image description here

QRY_TYPE (ID – unique identifier of type, С_NAME – name of type, C_AB_REF – link to the abonent, who processes these types of messages).

ST_ABONENTS (ID – unique identifier, С_NAME – name)

QRY_QUEUE (ID – unique identifier, С_IN_TIME – date and time of writing msg into table, C_EXEC_TIME – date and time of the msg processing, C_ST – processing status (null - didn't, 1 – successfull, 0 – error with processing), C_QRY_TYPE – link for the query type).

I need to make SQL query, which will show an HOUR, when CNT(count of messages in this time is max) - in other words the most high loaded hour for all abonents.

Result should look like this

enter image description here

Upvotes: 0

Views: 65

Answers (1)

dnoeth
dnoeth

Reputation: 60472

SELECT EXTRACT(HOUR FROM C_EXEC_TIME) AS hr
   ,COUNT(*) AS cnt
FROM
....
GROUP BY 
   EXTRACT(HOUR FROM C_EXEC_TIME)
ORDER BY 
   COUNT(*) DESC
FETCH FIRST ROW ONLY

If you Oracle version doesn't support FETCH:

SELECT *
FROM
 (
    SELECT EXTRACT(HOUR FROM C_EXEC_TIME) AS hr
       ,COUNT(*) AS cnt
       ,ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS rn
    FROM
    ....
    GROUP BY 
       EXTRACT(HOUR FROM C_EXEC_TIME)
  ) dt
WHERE rn = 1

Upvotes: 1

Related Questions