CODEWITHSUNDEEP
sqlsql-servert-sql
Can
Can

Reputation: 9

How can I use case when in order by?

I have a sample records. This records shown in below. I want to select only a process type. When I select process type I have a condition. If Max create date between this records bigger than max modify date then order by create date and select top 1 process type else if max modify date bigger than max create date order by modify date and select top 1 process type.

This is my query. I tried that query but I have an error. Process_type is invalid in the select list.

 select top 1
 process_type
from
#Result veh (nolock) 
where
veh.end_date is null
and veh.is_owner=0
and veh.relation_type=1
group by 
veh.fk_customer_id,
veh.fk_vehicle_id,
veh.relation_type,
veh.is_owner
HAVING COUNT(*) > 1
order by 
CASE WHEN  MAX(veh.create_date)>= MAX(veh.modify_date) THEN veh.create_date ELSE veh.modify_date END desc

This is all records.

pk_id customer_id vehicle_id relation_type end_date is_owner create_date modify_date process_type
1 100 200 1 null 0 2021-09-14 2021-09-13 4
2 100 200 1 null 0 2021-09-18 2021-09-13 6
3 100 200 1 null 0 2021-09-16 2021-09-13 5
4 100 200 1 null 0 2021-09-19 2021-09-13 3

This is expected record because this record has max create date between all records. After this expected records. I want to select process type from expected record.

pk_id customer_id vehicle_id relation_type end_date is_owner create_date modify_date process_type
4 100 200 1 null 0 2021-09-19 2021-09-13 3

Upvotes: 0

Views: 101

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You seem to want the record based on the maximum of create date or modify date. That would be:

select top (1) process_type
from# Result veh
where veh.end_date is null and
      veh.is_owner = 0 and
      veh.relation_type = 1
order by (case when modify_date > create_date then modify_date else create_date end)

If you want this per customer, then you can use row_number():

select top (1) process_type
from (select veh.*,
             row_number() over (partition by customerid order by (case when modify_date > create_date then modify_date else create_date end)) as seqnum
      from #Result veh
      where veh.end_date is null and
            veh.is_owner = 0 and
            veh.relation_type = 1
     ) veh
where seqnum = 1;

Upvotes: 0

Serg
Serg

Reputation: 22811

Select the last row in a series of two or more rows.

select process_type
from (
    select *
      , row_number() over(
                 partition by fk_customer_id, fk_vehicle_id, relation_type, is_owner 
                 order by CASE WHEN create_date >= modify_date THEN create_date ELSE modify_date END DESC) rn1
      , row_number() over(
                 partition by fk_customer_id, fk_vehicle_id, relation_type, is_owner 
                 order by CASE WHEN create_date >= modify_date THEN create_date ELSE modify_date END) rn2            
    from #Result
    where  end_date is null
       and is_owner=0
       and relation_type=100
) t
where rn1 = 1 and rn2 > 1;

Upvotes: 1

Related Questions