Query boolean and if statement

Question

so i have table name report the detail like this

No(autoincrement)       Code       Stats(bool)
--------------------------------------
       1                 F01        0
       2                 F02        0
       3                 F03        0
       4                 F03        1
       5                 F03        1
       6                 F04        1
       7                 F04        0

so the scenario is if code have value for stats is true then show them all and if the code didnt have value true then only show the false so the result i want is (note : one code always have 1 record for value false)

No(autoincrement)           Code       Stats
--------------------------------------------
           1                  F01        0
           2                  F02        0
           4                  F03        1
           5                  F03        1
           6                  F04        1

because f01 and f02 didnt have value stats true then show them and for case f03 and f04 because have value true for stat show them all true and ignore the false value

so how the query for i get the result

Which dbms you are using?

Jay Shankar Gupta
– Jay Shankar Gupta

2018-03-31 04:18:35 +00:00
Commented Mar 31, 2018 at 4:18 — Jay Shankar Gupta
– Jay Shankar Gupta, Commented Mar 31, 2018 at 4:18
Im using mysql for my dbms

Inheaven A
– Inheaven A

2018-03-31 04:37:08 +00:00
Commented Mar 31, 2018 at 4:37 — Inheaven A
– Inheaven A, Commented Mar 31, 2018 at 4:37
Check My Solution using MySql DenseRank @InheavenA

Jay Shankar Gupta
– Jay Shankar Gupta

2018-03-31 05:13:57 +00:00
Commented Mar 31, 2018 at 5:13 — Jay Shankar Gupta
– Jay Shankar Gupta, Commented Mar 31, 2018 at 5:13

Yogesh Sharma · Accepted Answer · 2018-03-31 04:44:04Z

1

It seems you want DENSE_RANK() function

select * from
(
    select *,
            dense_rank() over (partition by Code order by Stats desc) Seq
    from table t
) t
where Seq = 1

If, you don't want to go with subquery form, you could explore the above things via TOP with ties which available in SQL Server

select top 1 with ties [No(autoincrement)], Code, [Stats(bool)]
from table t
order by dense_rank() over (partition by Code order by [Stats(bool)] desc)

Since MySQL doesn't have analytical function. So, you could explore these thing via UNION ALL

select * 
from table t
where exists (
    select 1 from table
    where autoincrement  = t.autoincrement  and Stats = 1
) UNION ALL 
select * 
from table t
where not exists (
    select 1 from table
    where code = t.code and Stats = 1) 
ORDER BY 1

edited Mar 31, 2018 at 4:44

answered Mar 31, 2018 at 4:15

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Inheaven A Over a year ago

how to use dense_rank if im using mysql for query

Inheaven A Over a year ago

What is 1 from select 1 is ?

Yogesh Sharma Over a year ago

@InheavenA.. select 1 means return 1 for every matched record that satisfied the filtered criteria.

Jay Shankar Gupta · Accepted Answer · 2018-03-31 05:11:18Z

0

SELECT No,Code,Stats FROM
(SELECT No,Code,Stats,
  Case when @Code = t.Code Then
            Case when @stats != t.stats  Then
                 @DenseRank := @DenseRank + 1
            ELSE
                 @DenseRank := 1
            END
   Else
      @DenseRank := 1 
   END as DenseRank,
  @Code :=t.code as VarCode,
  @stats :=t.stats as varstatus
  FROM Table1 t join
  (Select @DenseRank := 1,@Code := '',@stats := '' )r
Order BY CODE,Stats DESC)
AS T1
WHERE
DenseRank=1
Order BY CODE,Stats DESC;

Output

No  Code    Stats
1   F01     0
2   F02     0
4   F03     1
5   F03     1
6   F04     1

Demo

http://sqlfiddle.com/#!9/f868a/3

answered Mar 31, 2018 at 5:11

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

Collectives™ on Stack Overflow

Query boolean and if statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related