How to find maximum value in a table

Question

Say in table A, we have application name and location where those applications are used.

I'm looking to find the location where an application is being used the most. In case of ties, both locations should be returned.

Table content:

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

Expected output:

Application   Max(Loc)
A             xy
B             ab

Answer 1

This can be solved using the aggregation function RANK. Use ROW_NUMBER instead to return only one row per application in case of ties.

Example on 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;