CODEWITHSUNDEEP
mysqlsql
mohan111
mohan111

Reputation: 8865

How to get Active Records along with Inactive Records

I have sample Data Set

ID   Name  Active 
1    Mii    0
1    Mii    1
2    Rii    0
2    Rii    1
3    Lii    0
4    Kii    0
4    Kii    1
5    Sii    0

How I can get active records along with Inactive records for other ID's.

ID   Name  Active 
1    Mii    1
2    Rii    1
3    Lii    0
4    Kii    1
5    Sii    0

I have taken all the data into 2 temp tables because lot of joins are there

select  * from  tmp1  where  active  = 1  
UNION ALL
select  * from  tmp2  where  active  = 0 AND  
    NOT  EXISTS  (SELECT  1  FROM  tmp1   WHERE  Active  = 1 )

can anyone tell me is there any better way to write in MYSQL

Upvotes: 0

Views: 441

Answers (2)

Atif
Atif

Reputation: 2210

Using analytical functions:

select * from (
SELECT ID, NAME, RANK() OVER ( ORDER BY ACTIVE desc) AS RN
FROM TABLE1) a where rn = 1;

Upvotes: 1

sticky bit
sticky bit

Reputation: 37467

Assuming that active can only be 0 or 1, aggregation could help:

SELECT id,
       name,
       max(active) active
       FROM elbat
       GROUP BY id,
                name;

max(active) is 1, if there is a record with the id and name that has a 1 in active, as 1 > 0. Otherwise it is 0, the only value.

Upvotes: 2

Related Questions