CODEWITHSUNDEEP
sql-server
Coco
Coco

Reputation: 39

How to fetch a particular column value which has same values in other columns of a table

Am have a following data: enter image description here

As there are 3 different results for Student_ID 111. I need a query to fetch the row which has the result as 'pass' as well fetch the row with student_ID 112 which as a result as Fail.

Thanks for your help!

Upvotes: 0

Views: 590

Answers (3)

user8232211
user8232211

Reputation:

You can try this..

SELECT * FROM `tblname` WHERE Result = 'Pass' OR Result = 'Fail'

Upvotes: 0

Robin
Robin

Reputation: 154

I would do it with row_number and partition by

create table #t(student_id int, name varchar(50), result varchar(50))
insert into #t values 
(111,'Jhon','Pass'),
(111,'Jhon','Absent'),
(111,'Jhon','Fail'),
(112,'Max','Fail');

WITH filterQuery as(
    select 
    ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY CASE result
                                                            WHEN 'Pass' THEN 0
                                                            WHEN 'Fail' THEN 1
                                                            WHEN 'Absent' THEN 2
                                                        END ASC) 
      AS Row , 
    * from #t
)
select student_id, name, result from filterQuery where Row = 1

DROP Table #t

Result

But the database design is not really good

Upvotes: 1

Dhaval Soni
Dhaval Soni

Reputation: 416

Can you please try below Query:

select * from Student where Result = 'Pass' Or (Student_ID = 112 And Result = 'Fail')

Upvotes: 0

Related Questions