Selecting row from group of row based on a column value

Question

Have following table

PkId   ProfileId    Status   Amount
1         234          0      10
2         235          1      100
3         236          0      50
4         236          1      80
5         237          0      70

For row number 3 and 4 Profile Id is same but I want in resultset it should consider row having value 0 so whereever we have rows where Profile Id is same it should pick row where status =0 And for rest of the rows it should pick as it is.

Expected Result Set:

 PkId   ProfileId    Status   Amount
    1         234          0      10
    2         235          1      100
    3         236          0      50
    5         237          0      70

In expected result set row number 4 has to be omitted because :

236 Id at 4 is already there row number 3
Value of Status is 1 in row number 4.

You first need to define what you want your output to be based on ProfileID or status. Then query can be written. — LearningToCode, Jul 06 '18 at 16:44
You need to use some analytical function like row_number over partition — James, Jul 06 '18 at 16:46
@Archit Yes in output or you can say in select I want status and Profile Id both. — Alex, Jul 06 '18 at 16:46

score 0 · Accepted Answer · answered Jul 06 '18 at 16:50

0

you could use a join on subselect for min status

select * from my_table m
inner join (
select ProfileId ,   min(Status ) min_status
from my_table 
group by ProfileId
) t on t.profileId = m.profileId and t.min_status = m.status

answered Jul 06 '18 at 16:50

ScaisEdge

131,976
10
91
107

Selecting row from group of row based on a column value

1 Answers1