0

I have the following table in Oracle:

category      group         lift
Apples        Lemons        8
Apples        Oranges       7
Apples        Bananas       9
Apples        Watermelon    7.5
Oranges       Pineapple     6
Oranges       Apples        2.5
Oranges       Lemons        4
Oranges       Strawberries  5.5

In real life, the category field contains too many rows for Apples and Oranges. What I want to do is to choose the distinct categories and the top 3 groups (group field) that correspond to the largest lifts for each category. Like this:

category      group         lift

Apples        Bananas       9
Apples        Lemons        8
Apples        Watermelon    7.5
Oranges       Pineapple     6
Oranges       Strawberries  5.5
Oranges       Lemons        4

As you see, I want to do a sort of "Group by" but not with any numerical metric. I just want to choose top 3 groups per category according to lift ordering.

Any ideas about how to this in Oracle?

Thanks in advance!

Moudiz
  • 7,211
  • 22
  • 78
  • 156
Gabriela M
  • 605
  • 1
  • 10
  • 25
  • 1
    Try selecting `row_number() over (partition by category order by lift)` and see if you can use that (in a having clause or with a cte). Ties could be a problem. – Mat Mar 28 '18 at 15:58
  • you want something similar to this ? https://stackoverflow.com/questions/134958/get-top-results-for-each-group-in-oracle – Moudiz Mar 28 '18 at 15:58
  • In all cases when you want the "top n" or "bottom n" by some measure (`lift` in your case), you must think about, and include in your requirement, what should happen when there are ties. So for example, what if the third and fourth highest `lift` in a `category` are equal? –  Mar 28 '18 at 16:01
  • What is your attempt so far? – Kaushik Nayak Mar 28 '18 at 16:02

1 Answers1

1

You must understand how dense_rank() is working. If you have equal values in top range for example: 9, 8, 7, 7, 6: this select return 4 top rows (9,8,7,7).

select FCATEGORY, FGROUP, FLIFT from (
    select f.*, dense_rank() over ( partition by f.FCATEGORY order by f.FLIFT desc ) rnk
    from fruits f )
where rnk <= 3
hekko
  • 292
  • 1
  • 2
  • 6