1

How can we construct a SELECT statement to select a results set when we are grouping by status and id but there is a third column?

Given a table where

ID  |   Status  |   Type
——————————————————————————
1   |   Started |   A
1   |   Done    |   B
1   |   Done    |   C
2   |   Started |   A
2   |   Done    |   B
2   |   Started |   C
3   |   Done    |   A
3   |   Done    |   B
3   |   Done    |   C
4   |   Started |   A
4   |   Started |   B
4   |   Started |   C
5   |   Done    |   A
5   |   Done    |   B
5   |   Done    |   C

Desired result:
Select Id’s with only status of all the types = done.

In above example select the result set would include only ID 3 & 5

3 | Done
5 | Done
1
  • 2
    Are you using Microsoft SQL Server or PostgreSQL, which database system?
    – J.D.
    Oct 9, 2022 at 4:01

1 Answer 1

4

Assuming:

  • there can be any number of rows per id
  • status is defined NOT NULL

This returns all IDs where all existing entries have status = 'Done':

SELECT id
FROM   tbl
GROUP  BY id
HAVING min(status) = max(status)
AND    min(status) = 'Done'
ORDER  BY id;  -- optional

All standard SQL.

2
  • I love this approach, as it can be extended to accommodate joins too. Is this an "abusive" use of min and max, or is there a more "just" way to express this in SQL? It feels like an unfair, clever hack - that I feel guilty to like.
    – ybakos
    Feb 3, 2023 at 5:28
  • 1
    @ybakos I hope telling you the query is completely legit will not curb your guilty pleasure. :) Can't think of a better way for this particular task. Feb 3, 2023 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.