Steven Dropper Steven Dropper - 4 months ago 7
SQL Question

Select a column value with the fewest occurrences

I'm not sure if it is possible to do it in the SQL itself with just one query but that's why I'm asking.

Is there a way to get the column value with the least occurrence in table?

A table would look like:

| Id | Value |
--------------
| 1 | Banana|
| 2 | Carrot|
| 3 | Apple |
| 4 | Apple |
| 5 | Banana|
| 6 | Apple |
| 7 | Apple |
| 8 | Banana|
| 9 | Apple |


Now that there's a "Carrot" value with the least occurrences, how would a SQL look like to get the value with the least occurrences? Kinda confusing and maybe you won't understand on the first try. Thanks.

Answer

Here is one method:

select value
from t
group by value
order by count(*)
fetch first 1 row only;

fetch first 1 row only is ANSI standard syntax, supported by several databases. Some spell it as limit or select top 1.