CODEWITHSUNDEEP
sqlsql-servercountmin
user3739958
user3739958

Reputation: 37

Select the record with minimum count

I have a setup like below:

Table - Tasks

| ResourceID |   Name   | T_SEQ | 
|    1       |  Res1    |   90  |
|    1       |  Res1    |   91  |
|    2       |  Res2    |   92  |

I am running the following:

select ResourceID, COUNT(ResourceID) 
from Tasks 
group by ResourceID

This shows me that Res1 has a count of 2 and Res2 has a count of 1.

Now I want to get the minimum, so in this case I need to get the ResourceID of 2 because it only has 1 job, but I'm not sure how to go about it?

Thanks for anyone who can help.

Upvotes: 1

Views: 901

Answers (2)

tobypls
tobypls

Reputation: 849

You can solve this using CTEs. This will give you all rows which have the minimum count:

; WITH occurrences AS
(
    select ResourceID, COUNT(ResourceID) AS Counts
    from Tasks 
    group by ResourceID
)
, min_occurrences AS
(
    SELECT MIN(Counts) AS min_counts FROM occurrences
)
SELECT ResourceID FROM occurrences a
INNER JOIN min_occurrences b ON a.Counts = b.min_counts

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

One method is order by and top:

select top 1 ResourceID, COUNT(ResourceID) 
from Tasks 
group by ResourceID
order by COUNT(ResourceID);

Upvotes: 3

Related Questions