0

I have data in my database that I need to group by and find the max value based on frequency. For example I want to select both the ID and the Country with the maximum NumberOfVisits per ID.

sample data:

ID Country NumberOfVisits
1 US 5
2 UK 7
1 UAE 10

The Output I am expecting is :

ID Country
1 UAE
2 UK
MT0
  • 143,790
  • 11
  • 59
  • 117
Sarah john
  • 71
  • 1
  • 7
  • In the sample data, you only need the row for country UAE right - that one has the highest NumberOfVisits. No group by is needed for that – Koen Lostrie Oct 14 '21 at 07:47
  • but I have many ID's and I would like to select both ID and country for each ID. In this example what I expect is : 1 | UAE 2 | UK – Sarah john Oct 14 '21 at 07:48

1 Answers1

1

With a little help of analytic functions (sample data in lines #1 - 8; query begins at line #9):

SQL> WITH
  2     test (id, country, num)
  3     AS
  4        (SELECT 1, 'US', 5 FROM DUAL
  5         UNION ALL
  6         SELECT 2, 'UK', 7 FROM DUAL
  7         UNION ALL
  8         SELECT 1, 'UAE', 10 FROM DUAL),
  9     temp
 10     AS
 11        (SELECT id,
 12                country,
 13                num,
 14                RANK () OVER (PARTITION BY id ORDER BY num DESC) rnk
 15           FROM test)
 16  SELECT id, country, num
 17    FROM temp
 18   WHERE rnk = 1;

        ID COU        NUM
---------- --- ----------
         1 UAE         10
         2 UK           7

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57