Oracle SQL get row with MAX value for each group in a set of grouped results

Question

I have a following set of result which i got by running

SELECT 
       REV_USAGE_DATA.DDATE, 
       REV_USAGE_DATA.SEGMENT, 
       COUNT(*) AS Freq
FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
GROUP BY 
       REV_USAGE_DATA.DDATE,
       REV_USAGE_DATA.SEGMENT
ORDER BY 
REV_USAGE_DATA.DDATE

For each date I want to get the whole row that has the highest value in the FREQ column. How can I achieve this?

score 1 · Accepted Answer · answered Mar 08 '19 at 14:58

1

You can use CTEs

WITH CTE0 AS 
(
  SELECT 
       REV_USAGE_DATA.DDATE, 
       REV_USAGE_DATA.SEGMENT, 
       COUNT(*) AS Freq
  FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
  GROUP BY 
       REV_USAGE_DATA.DDATE,
       REV_USAGE_DATA.SEGMENT
)
SELECT 
  DDATE,
  SEGMENT, 
  FREQ
FROM CTE0 
WHERE (DDATE, SEGMENT, FREQ) IN (
  SELECT DDATE, MAX(SEGMENT), MAX(FREQ)
  FROM CTE0 
  GROUP BY DDATE
)

answered Mar 08 '19 at 14:58

Derviş Kayımbaşıoğlu

28,492
4
50
72

worked like a charm. Thank you – tendaitakas Mar 09 '19 at 12:40

Gordon Linoff · Answer 2 · 2019-03-09T13:32:40.727

1

Use ROW_NUMBER():

SELECT DDATE, SEGMENT, Freq
FROM (SELECT REV_USAGE_DATA.DDATE, REV_USAGE_DATA.SEGMENT, 
             COUNT(*) AS Freq,
             ROW_NUMBER() OVER (PARTITION BY REV_USAGE_DATA.DDATE ORDER BY COUNT(*) DESC) as seqnum
      FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
      GROUP BY REV_USAGE_DATA.DDATE,REV_USAGE_DATA.SEGMENT
     ) rud
WHERE seqnum = 1
ORDER BY REV_USAGE_DATA.DDATE;

If you have ties on a date and you want all the highest values, then use RANK() instead.

edited Mar 09 '19 at 13:32

answered Mar 08 '19 at 14:59

Gordon Linoff

1,242,037
58
646
786

thank you but this is giving me 4 rows per date. Infact its reproducing the same result set that I shared in my question. – tendaitakas Mar 09 '19 at 12:37
@tendaitakas. . . Yup. It would do that wouldn't it? I added the where clause that does the filtering you want. – Gordon Linoff Mar 09 '19 at 13:32

score 0 · Answer 3 · answered Mar 08 '19 at 15:01

Plenty of similar solutions! Here's mine. Use a CTE to calculate which record has the highest frequency (similar to row-number) then select them

WITH data AS
(
  SELECT 
       REV_USAGE_DATA.DDATE, 
       REV_USAGE_DATA.SEGMENT, 
       COUNT(*) AS Freq
  FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
  GROUP BY 
         REV_USAGE_DATA.DDATE,
         REV_USAGE_DATA.SEGMENT
),
maxRecords AS
(
  SELECT DDATE, SEGMENT, FREQ, 
         CASE WHEN FREQ = MAX(FREQ) OVER(PARTITION BY DDATE) THEN 1 ELSE 0 END HighestFreq
  FROM data
)

SELECT DDATE, SEGMENT, FREQ
FROM maxRecords
WHERE HighestFreq = 1
ORDER BY DDATE;

Oracle SQL get row with MAX value for each group in a set of grouped results

3 Answers3