How to find the top or max Column value of a table?

Question

Having the following table which is some kind of an http request logs:

+---------+----------+-----------+----------+
| County  |  Mobile  |    OS     | Browser  |
+---------+----------+-----------+----------+
| USA     | iPhone   | iOS       | Safar    |
| UK      | Samsung  | Android   | Chrome   |
| India   | Motorola | Android   | Chrome   |
| India   | Nokia    | Android   | Chrome   |
| Russia  | Nokia    | Microsoft | Edge     |
| Germany | Sony     | Android   | Chrome   |
| India   | Sony     | Android   | Chrome   |
| Russia  | Nokia    | Microsoft | Edge     |
| Germany | Sony     | Android   | Chrome   |
| India   | Motorola | Android   | Chrome   |
| Russia  | Nokia    | Android   | Edge     |
| Germany | Sony     | Android   | Chrome   |
+---------+----------+-----------+----------+

How to find the most used mobile phone, OS, and browser per county and get a result like

+---------+----------+-----------+----------+
| County  |  Mobile  |    OS     | Browser  |
+---------+----------+-----------+----------+
| USA     | iPhone   | iOS       | Safar    |
| UK      | Samsung  | Android   | Chrome   |
| India   | Motorola | Android   | Chrome   |
| Russia  | Nokia    | Microsoft | Edge     |
| Germany | Sony     | Android   | Chrome   |
+---------+----------+-----------+----------+

So the most used mobile phone in USA is iPhone and the most used OS is iOs and the most used browser is Safar and so forth ...

Are you using MySQL or Oracle? (The answer won't be the same...) — jarlh, Aug 27 '18 at 07:47
Removed conflicting product tags. Pls add the one back that you use. — Shadow, Aug 27 '18 at 07:48
Hi, based on the information you have provided, it is not possible to determine the criteria for your expected output - there is no way of knowing what brands are more popular than others. Please amend your question to explain this. Also, are you using mysql or oracle? — Barry Piccinni, Aug 27 '18 at 07:48
@BarryPiccinni I would say the highest count per city is what the OP is looking for — Shadow, Aug 27 '18 at 07:50
See [here](https://stackoverflow.com/questions/tagged/greatest-n-per-group+sql) — , Aug 27 '18 at 07:50
@jarlh Doesn't matter I just want a clew how to go about this ... to be honest I am using Hive but I first want to know how to do it in MySQL or Oracle from there I can continue ... — Adelin, Aug 27 '18 at 07:50

score 2 · Accepted Answer · answered Aug 27 '18 at 08:08

If your DBMS support window function, you can try this.

Try to use CTE write subquery get the COUNT by County , Mobile,OS,Browser colnums and use row_number make row number by cnt then get rn=1.

CREATE TABLE T(
   County VARCHAR(50),
     Mobile VARCHAR(50),
     OS VARCHAR(50),
     Browser VARCHAR(50)
);


INSERT INTO T VALUES ('USA','iPhone', 'iOS', 'Safar');
INSERT INTO T VALUES ('UK','Samsung', 'Android' , 'Chrome');
INSERT INTO T VALUES ('India','Motorola','Android' , 'Chrome');
INSERT INTO T VALUES ('India','Nokia','Android', 'Chrome');
INSERT INTO T VALUES ('Russia','Nokia','Microsoft', 'Edge');
INSERT INTO T VALUES ('Germany','Sony','Android','Chrome');
INSERT INTO T VALUES ('India','Sony','Android','Chrome');
INSERT INTO T VALUES ('Russia','Nokia','Microsoft','Edge');
INSERT INTO T VALUES ('Germany','Sony','Android','Chrome');
INSERT INTO T VALUES ('India','Motorola','Android','Chrome');
INSERT INTO T VALUES ('Russia','Nokia','Android','Edge');
INSERT INTO T VALUES ('Germany','Sony','Android','Chrome');

Query 1:

with cte as (
  select County,Mobile,OS,Browser,COUNT(*) cnt
  from T
  GROUP BY County,Mobile,OS,Browser
)
SELECT 
    t1.County,
    t1.Mobile,
    t1.OS,
    t1.Browser 
FROM (
  select t1.*,row_number() over(partition by County order by cnt desc) rn
  from cte t1
) t1
where t1.rn = 1
ORDER BY COUNTY desc

Results:

|  COUNTY |   MOBILE |        OS | BROWSER |
|---------|----------|-----------|---------|
|     USA |   iPhone |       iOS |   Safar |
|      UK |  Samsung |   Android |  Chrome |
|  Russia |    Nokia | Microsoft |    Edge |
|   India | Motorola |   Android |  Chrome |
| Germany |     Sony |   Android |  Chrome |

I am trying it and it seems to work ... but can you please explain the part of the query "select t1.*,row_number() over(partition by County order by cnt desc) rn from cte t1" specially "from cte t1" do you join cte and t1 or ? — Adelin, Aug 27 '18 at 09:00
Because you seem to want to get the heigher amount by country. So you can try to use row_number make row number `order by` amount desc , then get top 1 by each country — D-Shih, Aug 27 '18 at 10:14
More information you can see doc https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm — D-Shih, Aug 27 '18 at 10:17

Fahmi · Answer 2 · 2018-08-27T07:57:09.963

0

Try this: it will work in oracle

select * from 
(select max(rn) rn1 ,country
from
(
select *, row_number over partition by (Mobile, OS,Browser order by County) as rn
from tablename)a)x inner join 
(
select *, row_number over partition by (Mobile, OS,Browser order by County) as rn
from tablename)b on b.rn=x.rn1 and b.country=x.country

edited Aug 27 '18 at 07:57

answered Aug 27 '18 at 07:51

Fahmi

37,315
5
22
31

How to find the top or max Column value of a table?

2 Answers2