SQL Query- To get a record specific to one particular value only-CodePudding

I have a table like below:

SID   GRADE
1       A
1       B
1       C
2       A
2       D
3       A
4       A
5       B

Now, I want only those records as an output wherein if a SID is only having grade as 'A', but no other grades-- those SIDs will be selected

Output will be:

SID     GRADE
3         A
4         A

I have already implemented this via below:

SELECT SID, MIN(GRADE)
FROM MYTABLE
GROUP BY SID
HAVING COUNT(GRADE) = 1
   AND MIN(GRADE) = 'A'

Just want to check if there is any other options available to achieve this output?

CodePudding user response：

If inside one SID there is no way to have multiple A grades then below query will be enough

select a.SID from MYTABLE a 
where a.grade = 'A' 
  and not exists (select * from MYTABLE b where b.SID = a.SID and b.grade != 'A')

In case, when grade may be null condiftion should be slight different

select a.SID from MYTABLE a 
where a.grade = 'A' 
  and not exists (select * from MYTABLE b where b.SID = a.SID and (b.grade != 'A' or b.grade is null))

And full version - where there may be multiple A grades inside one SID and grade may be null

select DISTINCT a.SID from MYTABLE a 
where a.grade = 'A' 
  and not exists (select * from MYTABLE b where b.SID = a.SID and (b.grade != 'A' or b.grade is null))

CodePudding user response：

Solution:

SELECT SID, 'A' AS GRADE
FROM (    
SELECT SID,
SUM(CASE WHEN GRADE <> 'A' THEN 1 ELSE 0 END) AS LOGIC
FROM MYTABLE
GROUP BY SID
)
WHERE LOGIC = 0
ORDER BY SID

CodePudding user response：

Do a GROUP BY. Use HAVING to return each SID which has MAX(GRADE) = 'A', i.e. no other grades.

SELECT SID, MAX(GRADE)
FROM MYTABLE
GROUP BY SID
HAVING MAX(GRADE) = 'A'

CodePudding user response：

I'd use STRING_AGG as an aggregation function. It's a little simpler as you save the count condition and it does also work for other grades (..which are not by chance at the beginning of the alphabet).

SELECT sid, string_agg(grade)
FROM MYTABLE
GROUP BY sid
HAVING string_agg(grade) = 'A'