So I have a table that looks like this
| Name | Group | DATE | control |
| ------------ | ------------ | ------------ | ------------ |
| ABC | NULL | 20 April 2023 | Null |
| XYZ | A | 19 April 2023 | Null |
| YYY | A | 19 April 2023 | Y |

and the query for this is suppose as follows:

select Name,Group,Date,control
 from  Customer
 order by date desc

What I want for my table to look like that it first sorts the data using date in desc order but for the cases where group has some value like in the table above there are two cases with group A , I want the one with control check Y to be on top of that group so basically order will remain the same. Just in case of group, the case with control unit Y should come first and then the remaining cases.

My table will now look like this
| Name | Group | DATE | control |
| ------------ | ------------ | ------------ | ------------ |
| ABC | NULL | 20 April 2023 | Null |
| YYY | A | 19 April 2023 | Y |
| XYZ | A | 19 April 2023 | Null |

I tried using case statement in order by like this

select Name,Group,Date,control
 from  Customer
 order by date,case when control='Y' then 1 else 0 desc

But this shows the case YYY at top . I want ABC to remain at top and in the cases of group only, the case with control check Y should come on top.

Update: Before:

order by clause: order by e.dateon desc

After:

order by clause:

ORDER BY CONVERT(date, e.dateon) desc,
  CASE WHEN c.IsControlUnit = 'Y' THEN 1 ELSE 0 END desc