CODEWITHSUNDEEP
mysqlsqlsyntax
Resurgent
Resurgent

Reputation: 555

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?

Upvotes: 0

Views: 68

Answers (2)

Mohammad Nurul Huda Rimon
Mohammad Nurul Huda Rimon

Reputation: 2525

Try this: You need to use ORDER BY DESC for get height number of year

SELECT Year, COUNT(*) as height_number
FROM results
GROUP BY Year
ORDER BY Year DESC
LIMIT 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269993

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;

Upvotes: 1

Related Questions