How to find the maximum value in the table

Question

Say in table A, we have the name and location of the application in which these applications are used.

I am looking to find the place where the application is used most. In the case of communications, both places must be returned.

Table Contents:

Application Location A xy A xy A ab B xy B ab B ab 

Expected Result:

 Application Max(Loc) A xy B ab 

Answer 1

This can be solved using the RANK aggregation function. Use ROW_NUMBER to return only one row for each application in case of associations.

Sample SQL Fiddle .

 SELECT application, location FROM ( SELECT application, location, RANK() OVER ( PARTITION BY application ORDER BY COUNT(*) DESC ) AS rn FROM t GROUP BY application, location ) x WHERE rn = 1;