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

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;

CodePudding user response：

You have to surround your result with a new select statement where you use the MAX function.

Check this article: https://www.w3schools.com/sql/sql_min_max.asp They have some executable examples as well.

CodePudding user response：

You can rather use a COUNT() Analytic Function such as

SELECT TO_CHAR(o2.submitted_date, 'dd-mm-yyyy hh24') AS HoursBasis, 
       COUNT(o1.order_id) OVER 
       (PARTITION BY TO_CHAR(o2.submitted_date, 'dd-mm-yyyy hh24')) AS count_id
  FROM order1 o1
  JOIN order2 o2
    ON o1.id = o2.order_id 
  JOIN order_sg3 o3
    ON o2.id = o3.id
  JOIN ship_group4 s
    ON o3.groups = s.group_id
 WHERE o2.order_id != o1.order_id
   AND o1.source IN ('cfrd')
   AND o2.submitted_date >= '16-JUL-2022 00.00.00.000' 
   AND o2.submitted_date <= '17-JUL-2022 23.59.59.000'
   AND ((o2.value NOT IN ('rest') AND o2.value IS NOT NULL) OR o2.value IS NULL)
 ORDER BY count_id DESC
 FETCH FIRST 1 ROW WITH TIES

where the logic for grouping is replaced by partitioning, and the first matching row(s) return(s) in order to pick the maximum value(s) of count ( WITH TIES option is used by considering them(maximum values) might occur more than once )

CodePudding user response：

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;