Search code examples
sqloracle-databasemax

How to get the max value from count value in SQL Oracle

I have below SQL Query and query output where I am getting hourly basis order count for a specific date time range. Now out of this output I want such row (both date and count) whose count value is maximum.

SELECT TO_CHAR(b.submitted_date, 'dd-mm-yyyy hh24') AS HoursBasis,
       COUNT(a.order_id) AS count_id
  FROM order1 a, order2 b, order_sg3 c, ship_group4 d
 WHERE a.id = b.order_id
   AND b.order_id != a.order_id
   AND b.id = c.id
   AND c.groups = d.group_id
   AND a.source in ('cfrd')
   AND (b.submitted_date >= '16-JUL-2022 00.00.00.000' AND
        b.submitted_date <= '17-JUL-2022 23.59.59.000')
   AND b.value NOT IN ('rest')
 GROUP BY TO_CHAR(b.submitted_date, 'dd-mm-yyyy hh24')
 ORDER BY TO_CHAR(b.submitted_date, 'dd-mm-yyyy hh24') DESC;

enter image description here

Solution

  • Order by the count (rather than the date) and then use FETCH FIRST ROW ONLY (or WITH TIES):

    SELECT TO_CHAR(b.submitted_date, 'dd-mm-yyyy hh24') AS HoursBasis,
       COUNT(a.order_id) AS count_id
FROM   order1 a
       INNER JOIN order2 b
       ON (   a.id       = b.order_id
          AND b.order_id != a.order_id )
       INNER JOIN order_sg3 c
       ON (b.id = c.id)
       INNER JOIN ship_group4 d
       ON (c.groups = d.group_id)
WHERE  a.source in ('cfrd')
   AND b.submitted_date >= DATE '2022-07-16'
   AND b.submitted_date <  DATE '2022-07-18'
   AND b.value != 'rest'
GROUP BY TO_CHAR(b.submitted_date, 'dd-mm-yyyy hh24')
ORDER BY COUNT(a.order_id) DESC
FETCH FIRST ROW ONLY;