SQL: Retrieve value from a column that occurred least number of times

Question

I have a table which have a single field. and it have a values like (3,7,9,11,7,11) Now I want a query which will pick the value that occurred least number of times and if there is a tie with minimum occurrences then use the smallest number

In this case the answer will be 3.

Answer 1

Something like this:

SELECT TOP 1 COUNT(*), myField
FROM myTable
GROUP BY (myField)
ORDER BY COUNT(*) ASC

ADDITIONAL: And to taking into account the tie-breaker situation:

SELECT TOP 1 COUNT(*), myField
FROM myTable
GROUP BY (myField)
ORDER BY COUNT(*) ASC, myField ASC

Answer 2

In MySQL and PostgreSQL:

SELECT  *
FROM    (
        SELECT  field, COUNT(*) AS cnt
        FROM    mytable
        GROUP BY
                field
        ) q
ORDER BY
        cnt, field
LIMIT 1

Answer 3

Assuming you're using SQL Server: if you have ties for the least frequent number, and you want all ties returned, then you could do something like this:

DECLARE @temp table (
    count int,
    myField int
)

INSERT @temp
SELECT COUNT(*), myField
FROM myTable
GROUP BY (myField)

DECLARE @minCount int

SELECT @minCount = MIN(count)
FROM @temp

SELECT count, myField
FROM @temp
WHERE count = @minCount