Link to home
Start Free TrialLog in
Avatar of spirose
spirose

asked on

SQL Query syntax

UserID      StudentName      Result
1               John S      P
1               John S      F
2              Jane M      F
3             Jack R      P
 

Table A (See above)

Problem:
I need to only select one row per UserID. If there are 2 records and the Result is P for one and F for another, I need to only pick the F by default. That is as long as a F Result exists for a UserID, I need to pick that ignoring the P record (if there are 2 or more records for a userID)
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Select 1 userid, 'A' sn, 'P' r into #temp

insert into #temp
select 1, 'A', 'F'
union
select 2, 'B', 'F'
union
select 3, 'C', 'P'


select userid, sn, MIN(r) as r
from #temp
group by userid, sn


drop table #temp


Just update the bold part to reflect your table and columns.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial