How to find the highest populated instance in a column in SQL.

Question

Hey,

So I have a table (person), that contains columns such as persons name, age, eye-color, favorite movie.

How do I find the most popular eye color(s), returning just the eye color (not the count) using SQL (Microsft Access), without using top as there might be multiple colours with the same count.

Thank you

Answer 1

SELECT
  EyeColor
FROM
  Person
GROUP BY  
  EyeColor
HAVING
  COUNT(*) = (
    SELECT MAX(i.EyeColorCount) FROM (
      SELECT COUNT(*) AS EyeColorCount FROM Person GROUP BY EyeColor 
    ) AS i
  )

Answer 2

In Access, I think you need something on the lines of:

SELECT First(t.Eyecolor) AS FirstOfEyeColor
FROM (SELECT p.EyeColor, Count(p.EyeColor) AS C
FROM Person p
GROUP BY p.EyeColor
ORDER BY Count(p.EyeColor) DESC)  AS t;