CODEWITHSUNDEEP
sqlgroup-bycase
Hamed
Hamed

Reputation: 797

SQL select row IF condition satisfied Otherwise find a row with max Date

I have the following data structure

Date1         |  Date2               | a      | b
2018-11-15    |  2010-11-15          | 1      | null
2018-11-15    |  2011-11-15          | 0      | 300
2018-11-15    |  2012-11-15          | 0      | 100

2018-11-14    |  2010-11-15          | 1      | 200
2018-11-14    |  2012-11-15          | 0      | 100

2018-11-13    |  2010-11-15          | 1      | null
2018-11-13    |  2011-11-15          | 0      | 100

For each unique Date1 I need to

So ideally I would like to get 

Date1         |  Date2               | a      | b
2018-11-15    |  2012-11-15          | 0      | 100
2018-11-14    |  2010-11-15          | 1      | 200
2018-11-13    |  2011-11-15          | 0      | 100

I assume I need to combine Group BY Date1 and CASE THEN conditions but could figure out how that should work. Appreciate any hint.

Upvotes: 0

Views: 709

Answers (2)

forpas
forpas

Reputation: 164099

You can do it with UNION ALL and NOT EXISTS:

select * from tablename
where a = 1 and b is not null
union all
select t.* from tablename t
where 
  not exists (
    select 1 from tablename 
    where date1 = t.date1 and (date2 > t.date2 or (a = 1 and b is not null))
  )

See the demo.
Results:

| Date1               | Date2               | a   | b   |
| ------------------- | ------------------- | --- | --- |
| 2018-11-15 00:00:00 | 2012-11-15 00:00:00 | 0   | 100 |
| 2018-11-14 00:00:00 | 2010-11-15 00:00:00 | 1   | 200 |
| 2018-11-13 00:00:00 | 2011-11-15 00:00:00 | 0   | 100 |

Upvotes: 1

GMB
GMB

Reputation: 222482

You can use row_number() with conditional ordering:

select date1, date2, a, b
from (
    select 
        t.*,
        row_number() over(
            partition by date1 
            order by 
                case when a = 1 and b is not null then 0 else 1 end, 
                date2 desc, 
                b desc
            ) rn
    from mytable t
) t
where rn = 1

It is quite unclear whether you want to partition by just date1 or date1 and date2. Since you said you want the record with max date2, I assumed that you want partitions of date1 and a sorting criteria on date2 desc.

Finally, I added a third sorting criteria b desc to try and make the results (more) predictable if there are ties in the first two criterias.

Upvotes: 1

Related Questions