Resurgent Resurgent - 1 month ago 7
SQL Question

SQL Count Instances of Value and Order by highest number, where value is a year

I have an SQL Table as follows

Year

2007

2007

2007

2007

2006

2006

2006

2006

Year is of type VARCHAR(4) and is not of type DATE. In instances where the number of Years are equal (here - 4-2006, 4-2007), I would like the later year to be the year selected.

Here is my SQL Query:-

SELECT `Year`, COUNT(*) FROM `results` GROUP BY `Year` LIMIT 1


However, it returns 2006 and not 2007. I tried using an ORDER BY and/or dropping the GROUP BY at the same time but to no effect.

How can I achieve this?

Answer

You need an ORDER BY. Just to get the most recent year:

SELECT `Year`, COUNT(*)
FROM `results`
GROUP BY `Year`
ORDER BY `Year` DESC
LIMIT 1;

I'm not sure I know what you mean by: "here the number of Years are equal". However, I speculate:

SELECT `Year`, COUNT(*)
FROM `results`
GROUP BY `Year`
ORDER BY COUNT(*) DESC, `Year` DESC
LIMIT 1;