How to select multiple rows in sql based on multiple min values of a column-CodePudding

I have the following SQL query and the result of the query is shown below. How do i select multiple minimum values based on cnt column?

SELECT a.id , COUNT(a.id) cnt
from table1 a
where a.id in (SELECT id from table2 WHERE name = 'abc') 
GROUP BY a.id

Output of the above query

"id"    "cnt"
1003    3
1008    1
1011    2
1017    1

I would like the output to be

"id"    "cnt"
1008    1
1017    1

Any help is appreciated. Thank you.

CodePudding user response：

You can using having to check

SELECT a.id , COUNT(a.id) cnt
from table1 a
where a.id in (SELECT id from table2 WHERE name = 'abc') 
GROUP BY a.id
HAVING COUNT (a.id) =
    (SELECT MIN(cnt) FROM (SELECT a.id , COUNT(a.id) cnt
    from table1 a
    where a.id in (SELECT id from table2 WHERE name = 'abc') 
    GROUP BY a.id) as latest)

Check out this db fiddle

CodePudding user response：

You could use a Having to get the Min count.

SELECT a.id , COUNT(a.id) cnt
FROM table1 a
WHERE a.id in (SELECT id from table2 WHERE name = 'abc') 
GROUP BY a.id
HAVING COUNT(id) = (SELECT MIN(cnt)                              
           FROM (SELECT COUNT(a.id) cnt
           FROM table1 a
           WHERE a.id in (SELECT id from table2 WHERE name = 'abc') 
           GROUP BY a.id) b);