Query result based on other column-CodePudding

I need to filter out items, not sure what is best to do it.

I need to filter out when the Overwrite is true, then it will remove when "date" and "time" has same value's record with the "Overwrite" is true from the result.

For example: The records in the DB:

Date                Time          Name                 Overwrite
=======             ====        ===========            ======
10/01/2022          04:00       John White             false
11/01/2022          20:00       John Lee               false
10/01/2022          04:00       Tom Lee                true
12/01/2022          04:00       John Lee               false 

Query result want:

Date            Time        Name          Overwrite
=======         ======      ===========   ==========
10/01/2022      04:00       Tom Lee        true
11/01/2022      20:00       John Lee       false
12/01/2022      04:00       John Lee       false

Thanks!

CodePudding user response：

You may try correlated subquery using the EXISTS Clause as the following:

Select T.Date, T.Time, T.Name, T.Overwrite
From Tbl T 
Where
Not Exists (
             Select 1 From Tbl D 
             Where D.Overwrite='true' And T.Overwrite='false'
                   And D.Date=T.Date And D.Time=T.Time
           )
Order By T.Date

See a demo from db<>fiddle.