How would i use the most recent value for a case when statement?-CodePudding

I'm trying to use a case when off of a column value that needs to be from the most recent date. How would i do that without calling the table on itself such as:

select t1.id,t1.actual_date,t1.total_oec_owned,t1.flag,
CASE 
  WHEN t1.total < 5000000 THEN 'Small'
  WHEN t1.total>= 5000000 AND total < 10000000 THEN 'Medium'
  WHEN t1.total>= 10000000 THEN 'Large'
  ELSE NULL END AS branch_size_flag
from table.example t1
where t1.actual_date = (select max(t2.actual_date)
from table.examplet2
where t2.id= t1.id)

The issue with this is my query is extremely large with nearly 100 columns so I feel there must be a more efficient way. Any advice would help

CodePudding user response：

The so-called analytic functions (also known as window functions) were designed precisely for this purpose.

That is they allow you to avoid having to do self joins on your table.

In this case if you are looking for the last row in a group, row_number is the standard method.

The syntax is

row_number() over (partition by *column* order by *column*)

It assigns to each row of the group an index number. The "partition by" clause is much like a group by clause, because it specifies the group of rows to operate on. It also has an "order by" clause so you can define the ordering to the first row (or the last if you order descending).

In your case you would want to order by actual_date desc (meaning descending) and select only the row_number=1. That's why we have the subselect but no extra join.

Here is how it would look in your SQL:

select 
t1.id,t1.actual_date,t1.total_oec_owned,t1.flag,
CASE 
  WHEN t1.total < 5000000 THEN 'Small'
  WHEN t1.total>= 5000000 
     AND total < 10000000 THEN 'Medium'
  WHEN t1.total>= 10000000 THEN 'Large'
 ELSE NULL 
 END AS branch_size_flag
from 
(select 
  *, 
  row_number() over (partition by id order by edate desc) as r
  from table.example 
 ) t1
 where r=1