CODEWITHSUNDEEP
androidsqlandroid-room
Rajnikant B
Rajnikant B

Reputation: 126

Select unique Records based on a column by conditionally removing the other record having same column value based on some other coumn in sql

I am developing an Android app in which I am using the Room persistence library. In one of the Tables (Room Entity), I have a case where I want to select unique Records based on a column by conditionally removing the other record having the same column value based on some other column.

Consider the following table as a base.

S_ID STATUS
1 Pending
1 Rejected
3 Approved
4 Approved
5 Pending
6 Rejected
7 Rejected

the expected result of the SQL query where I want a record with 'Rejected' status to be removed from results if its S_ID collides with another 'Pending' status record

S_ID STATUS
1 Pending
3 Approved
4 Approved
5 Pending
6 Rejected
7 Rejected

what I have tried so far:

select DISTINCT s_id OR s_id = 0, * from TABLE1

Upvotes: 0

Views: 80

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use aggregation for this:

select s_id, min(status) as status
from table_id
group by s_id;

This uses the fact that alphabetical ordering is used for max() and 'Pending' < 'Rejecdted'.

Upvotes: 1

Parth M. Dave
Parth M. Dave

Reputation: 1163

I think You can try below query :

SELECT * FROM table1 WHERE table1.S_ID in (SELECT S_ID FROM table1 GROUP BY S_ID HAVING COUNT(S_ID)>0) and table1.[status]<>'Rejected'

Edited :

SELECT table1.S_ID, 
(select Alias.[Status] from Table1 Alias where Alias.S_ID=table1.S_ID  
and ((COUNT(table1.S_ID)>1 and Alias.[Status]<>'Rejected') or COUNT(table1.S_ID)=1) 
) as [Status]
FROM table1 GROUP BY S_ID HAVING COUNT(S_ID)>0

Upvotes: 2

Related Questions