[SOLVED] A subquery has set of 10 records with 5 columns , outer query has to create a column based on value inside the subquery

A subquery has set of 10 records with 5 columns , outer query has to create a column based on value inside the subquery

For eg : Subquery

A subquery has returned rows with :

(Select roll_no , name , subject, mark , result from tablea )

Roll_no     Name   Subject         Mark   Result
 1           J       Math           90     P
 1           J       English        80     P
 1           J       Lang           30     F
 1           J       Science        60     P
 1           J       History        70     P

I want main query to create a indicator with all value from subquery and if one record in the subquery has value F in the result column for the above 5 records , then create 5 records in main-query with grade F for all records else P in all 5 records in main query

select a.* , grade from (Select roll_no , name , subject, mark , result from tablea ) as a

Roll_no     Name   Subject         Mark   Result    Grade
 1           J       Math           90     P          F
 1           J       English        80     P          F 
 1           J       Lang           30     F          F
 1           J       Science        60     P          F
 1           J       History        70     P          F

Solution

Using a window function, you could do the following:

Select roll_no , name , subject, mark , result, 
  MIN(result) OVER () as Grade
from tablea

That's going to return the min(result) of the result set and repeat for each record, so even if one F appears, then every record will show F.

dbfiddle here