2

We need to group data in an Oracle database and must get a ProductNumber and a Description. The Description needs to be the one with the most occurrences inside the table. Both are a varchar2. See below for the raw data:

ProductNumber Description
ABC Product ABC (with discount)
ABC Product ABC
ABC Product ABC
DEF Product DEF

Expected result:

ProductNumber Description
ABC Product ABC
DEF Product DEF

We've tried this but don't find a way to include the Description with most occurences.

select distinct cnt1.ProductNumber
from (select COUNT(*) as total, ProductNumber
      from Inventory
      group by ProductNumber) cnt1,
     (select MAX(total) as maxtotal, ProductNumber
      from (select COUNT(*) as total, ProductNumber 
            from Inventory 
            group by ProductNumber)
      group by ProductNumber) cnt2
where cnt1.total = cnt2.maxtotal;

Here you could find a fiddle.

Do you know how we could do it?

2
  • 1
    In future, could you please provide a fiddle with your tables and data - it helps those who are trying to help you!
    – Vérace
    Jan 18, 2021 at 14:21
  • @Vérace: I've added one Jan 18, 2021 at 15:46

2 Answers 2

2

Here is one way to do it using window functions:

select ProductNumber, Description
from (
  select ProductNumber, Description
       , row_number() over (partition by productnumber order by cnt1, cnt2 desc) as rn
  from (
    select ProductNumber, Description
         , count(1) over (partition by ProductNumber) as cnt1
         , count(1) over (partition by ProductNumber, Description) as cnt2
    from inventory
  ) t
) u 
where rn = 1;

EDIT: Added plans for a slightly bigger set of data, Fiddle

Verace query

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |      1 |        |    676 |00:00:00.01 |     122 |       |       |          |
|   1 |  SORT UNIQUE               |           |      1 |  17576 |    676 |00:00:00.01 |     122 | 46080 | 46080 |40960  (0)|
|*  2 |   HASH JOIN SEMI           |           |      1 |  17576 |  17576 |00:00:00.01 |     122 |  2178K|  2050K| 2666K (0)|
|   3 |    TABLE ACCESS FULL       | INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
|*  4 |    VIEW                    |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|*  5 |     WINDOW SORT PUSHED RANK|           |      1 |  17576 |    676 |00:00:00.01 |      61 | 48128 | 48128 |43008  (0)|
|   6 |      HASH GROUP BY         |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1214K|  1214K| 1854K (0)|
|   7 |       TABLE ACCESS FULL    | INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T1"."DESCRIPTION"="I2"."DESCRIPTION")
   4 - filter("T1"."RN"=1)
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY "I1"."PRODUCTNUMBER" ORDER BY COUNT(*) DESC )<=1)

H. Pauwelyn's modified query

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |    676 |00:00:00.01 |     122 |       |       |          |
|   1 |  HASH UNIQUE          |           |      1 |    176 |    676 |00:00:00.01 |     122 |  2170K|  2170K| 1427K (0)|
|*  2 |   HASH JOIN RIGHT SEMI|           |      1 |    176 |    676 |00:00:00.01 |     122 |  2546K|  2546K|  654K (0)|
|   3 |    VIEW               |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|   4 |     HASH GROUP BY     |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1520K|  1520K| 1882K (0)|
|   5 |      TABLE ACCESS FULL| INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
|   6 |    VIEW               |           |      1 |  17576 |    676 |00:00:00.01 |      61 |       |       |          |
|   7 |     HASH GROUP BY     |           |      1 |  17576 |    676 |00:00:00.01 |      61 |  1520K|  1520K| 1882K (0)|
|   8 |      TABLE ACCESS FULL| INVENTORY |      1 |  17576 |  17576 |00:00:00.01 |      61 |       |       |          |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CNT1"."TOTAL"="CNT2"."MAXTOTAL")

Lennart's query

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |    676 |00:00:00.03 |     208 |       |       |          |
|*  1 |  VIEW                    |      |      1 |  17576 |    676 |00:00:00.03 |     208 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |  17576 |    676 |00:00:00.03 |     208 |  1116K|   557K|  991K (0)|
|   3 |    VIEW                  |      |      1 |  17576 |  17576 |00:00:00.02 |     208 |       |       |          |
|   4 |     WINDOW BUFFER        |      |      1 |  17576 |  17576 |00:00:00.02 |     208 |  1116K|   557K|  991K (0)|
|   5 |      INDEX FULL SCAN     | IX1  |      1 |  17576 |  17576 |00:00:00.01 |     208 |       |       |          |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PRODUCTNUMBER" ORDER BY "CNT1",INTERNAL_FUNCTION("CNT2") DESC 
              )<=1)
5
  • I would be grateful if you could look at my answer and fiddle here - it looks at performance and I'm not sure how either query would do with realistic datasets - I'd appreciate any ideas/thoughts or comments which you may have - +1 for a nice use of ROW_NUMBER() in conjunction with using COUNT() as a window function!
    – Vérace
    Jan 19, 2021 at 7:46
  • Impressive work with the fiddle. I will have a look later because today is an endless meeting day:-) Jan 19, 2021 at 9:20
  • @Vérace, I have added some more sample data to dbfiddle.uk/… . I created a slight skew by using t1 twice for description. For reasons unknown to me, the plan for your query is missing. I have not had the time to study it more closely. I also added an index to the table Jan 19, 2021 at 10:19
  • Thanks for your input - am looking at it - but I also have stuff to do... The thing about the plan for my own query being missing - I seem to have stumbled on some sort of subtle bug in either db<>fiddle or Oracle... Even when I run your fiddle - the plan for my query disappears - whereas the two others work. I have a running fiddle (with my query plan present - double check by running it and then copy and paste the URL to a different tab and it stops working!!! Not sure what's going on. I'll benchmark your stuff this evening and report back tomorrow - and thanks again. May have found bug?
    – Vérace
    Jan 19, 2021 at 12:11
  • I managed to fix that by removing the newline (I think): dbfiddle.uk/… Jan 19, 2021 at 12:22
2

I looked at this - it's an interesting question (+1).

I also added sample data - it's always difficult to cover edge-cases, but it's worth looking at (data and entire analysis on fiddle here).

So, we have:

SELECT * FROM inventory;

Result:

PRODUCTNUMBER   DESCRIPTION
          ABC   Product ABC (with discount)
          ABC   Product ABC
          ABC   Product ABC
          DEF   Product DEF
          XYZ   Product XYZ   --  <<< extra data from here.
          XYZ   Product XYZ
          XYZ   Product XYZ
          XYZ   Product XYZ (with discount 1)
          XYZ   Product XYZ (with discount 2)
          XYZ   Product XYZ (with discount 3)
          RST   Product RST with discount       -- the interesting two records!
          RST   Product RST without discount

I formulated the following SQL (I've shown the early steps that led me to my answer - partly for your understanding, partly for my own! :-) ):

SELECT 
  DISTINCT 
  t2.ProductNumber AS "Product Number", 
  t1.Description   AS "Product Desc." 
FROM
(
  SELECT i1.Description, COUNT(i1.Description) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY i1.ProductNumber ORDER BY COUNT(i1.Description) DESC) AS rn
  FROM inventory i1
  GROUP BY i1.Description, i1.ProductNumber
) t1
INNER JOIN
(
  SELECT i2.ProductNumber, i2.Description
  FROM inventory i2
) t2
ON t1.Description = t2.Description
WHERE t1.rn = 1
ORDER BY 1;

Result:

Product Number  Product Desc.
           ABC  Product ABC
           DEF  Product DEF
           RST  Product RST without discount
           XYZ  Product XYZ

Now, notice that my SQL has chosen the RST, Product RST without discount record where there was a choice between both RST (with and without discount) records.

Now, you will see from the fiddle that Lennart's query returns the other RST record.

You might want to investigate that and ensure that you retrieve the records that you require under all circumstances.

I've also looked at the plans for the queries. I found out how to do this using the fiddle from an article by Franck Pachot here and the associated Oracle fiddle here.

Now, I'm far from an expert on Oracle plans, but given their similarity, I can't tell whether Lennart's query or my own would be the more efficient given an large dataset. I would urge you to test with realistic datasets.

The only "concrete" benefit that accrues from my query is that it can be used on antique versions of servers that don't support ROW_NUMBER() - but even MySQL has those now, so it's unlikely to a significant gain! :-)

p.s. you will notice that I have modified your query slightly to use ANSI joins as follows:

select /*+ gather_plan_statistics */ distinct cnt1.ProductNumber
from 
(
  select COUNT(*) as total, ProductNumber
  from Inventory
  group by ProductNumber
) cnt1
JOIN
(
  select MAX(total) as maxtotal, ProductNumber
  from 
  (
    select COUNT(*) as total, ProductNumber 
    from Inventory 
    group by ProductNumber
  ) t
  group by ProductNumber
) cnt2
ON cnt1.total = cnt2.maxtotal;

I hope you find this helpful and/or more readable? I think that yours performs a CROSS JOIN and then filters which can be problematic with large datasets?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.