I have two tables and I have trouble figuring out how to do the order by statement to fit my needs.
Basically if the `FeaturedUntil` column if greater than now then these should be returned first ordered by the `PurchasedAt` column. Most recent purchases should be first. After these everything should be ordered by the item `Id` column descending.

**Create Table Script**

```
create table Items(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(200) null,
) 

create table Feature(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PurchasedAt] [datetime2](7) NOT NULL,
    [FeaturedUntil] [datetime2](7) NOT NULL,
    [ItemId] [int] NOT NULL,
)

```

**Insert Script**

```
insert into Items(Name) values ('test1')
insert into Feature(PurchasedAt, FeaturedUntil, ItemId) values (dateadd(day, -3, getdate()), dateadd(month, 1, getdate()), @@IDENTITY)
insert into Items(Name) values ('test2')
insert into Feature(PurchasedAt, FeaturedUntil, ItemId) values (dateadd(day, -2, getdate()), dateadd(month, 1, getdate()), @@IDENTITY)
insert into Items(Name) values ('test3')
insert into Feature(PurchasedAt, FeaturedUntil, ItemId) values (dateadd(day, -1, getdate()), dateadd(month, -1, getdate()), @@IDENTITY)
insert into Items(Name) values ('test4')

```

**Select Script**

```
select * 
from Items i 
left join Feature f on i.Id = f.ItemId
order by 
case when f.FeaturedUntil is not null THEN f.PurchasedAt
else  i.Id 
end

```

The `select` should return test2 first as it's `FeaturedUntil` is greater than now and it is the most recently purchased, second row should be test1 as it is bought before test2. After these should be test4 and last one is test3, because these have no joining `Feature` table data or the `FeatureUntil` is not greater than now and these are order by their `Item.Id` descending.