Multiple sort order in single query

Transact-SQL
1

Have 2 tables item and order- Needed is to display item based on latest order date, which is not within 30days order date falls in, but exception is fruits need to come on top

Declare @itemMaster table (itemid int,itemName varchar(50))

Insert Into @itemMaster
Values (1, 'Vegetable')
, (2, 'Apple')
, (3, 'Table')
, (4, 'Orange')
, (5, 'Mango')

select * from @itemMaster

Declare @orderMaster table (orderid int,orderDate datetime,itemid int)

Insert Into @orderMaster
Values (101, '2021-08-01',2)
, (102, '2021-08-02',4)
, (103, '2021-08-20',3)
, (104, '2021-08-15',1)

select * from @orderMaster

select om.itemid,orderDate
from @orderMaster om
inner join @itemMaster im on om.itemid = im.itemid
order by orderDate desc

My tried query is below. Would like to know any alternate way to do without using inner join? will there be any performance difference?
select om.itemid
,orderDate
,case when im.itemid in (2,4,5) then 1 else 0 end as sortorder
from @orderMaster om
inner join @itemMaster im on om.itemid = im.itemid
order by sortorder desc,orderDate desc

2

We're going to need something in the query to tell us the item is a fruit. We can't go just by the name.

3

I see you hardcoded itemid to 2,4,5. As Scott said, you should ideally have an ItemType in the table where 1 = Vegetable, 2 =Fruit, 3 = whatever so it's not hardcoded. You also mentioned you need the latest order that is not within 30 days. Does that mean the last order that is greater than today - 30 days? Lastly, what is the exception about fruit coming on top? Is fruit exempt from the 30 days? Or are you just looking for all orders that are more than 30 days old and you want to order it with fruit first, then the rest of the order? If no fruit is ordered, does that go last? Of it any fruit, then put that order first? You can see how easily the confusion sets in

4

This table is a test data for original records

  1. The real scenario is orders of specific type has most valid data
    which can be used for further carrier related api calls and hence want to bring that orders first.

  2. Date filter want to consider data which is not coming within one month
    as its already taken in other section