CODEWITHSUNDEEP
sqloracle-database
Doniu
Doniu

Reputation: 115

Select MAX issue SQL

Scenario:

I have a table with order status, for example:

/   ORDER    LOG NUMBER   LOG CODE
1   1        1            Preparing
2   1        2            Prepared
3   1        3            Sent
4   2        1            Preparing
5   2        2            Prepared
6   3        1            Preparing

I've been looking for a way to select orders, where last log code is Prepared.

For example I want to see all ORDERS where last LOG CODE is Prepared (last log)

Upvotes: 0

Views: 55

Answers (3)

dnoeth
dnoeth

Reputation: 60462

Oracle supports Windowed Aggregates:

select *
from 
 ( select 
      ORDER
      ,LOG_NUMBER
      ,LOG_CODE
      -- last log number for each order 
      ,max(LOG_NUMBER) over (partition by ORDER) as maxnum
   from mytable
 ) dt
where LOG_NUMBER = maxnum
  and LOG_CODE = 'Prepared'

or

select *
from 
 ( select 
      ORDER
      ,LOG_NUMBER
      ,LOG_CODE
      -- youngest row gets row_number 1
      ,ROW_NUMBER() over (partition by ORDER order by LOG_NUMBER desc) as rn
   from mytable
 ) dt
where rn = 1
  and LOG_CODE = 'Prepared'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269593

A pretty efficient way is to use correlated subqueries:

select t.*
from t
where t.lognumber = (select max(t2.lognumber) from t t2 where t.order = t2.order) and
      t.logcode = 'Prepared';

Upvotes: 1

Repi
Repi

Reputation: 68

You can use the analytic function to do so

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm

It should be something like:

SELECT order LAG(log_code, 1, 0) OVER (PARTITION BY order ORDER BY log_number) AS prev_code FROM orders

This will at least deliver you a resultset which has the last code information.

Instead of using an outer select you should be able to extend the query with

"having prev_code = 'Prepared'"

Upvotes: 1

Related Questions