Question 114508 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to select multiple rows in sql based on multiple min values of a column

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search