CODEWITHSUNDEEP
databaseoracle-databasemaxrecord
Nike Angreni
Nike Angreni

Reputation: 71

get the record based on max value oracle multiple tables

i got problem with oracle query. in this query i want to show status resume where the record is taken by max value of seq coloumn and extern coloumn. this is my query:

select x.order_id, z.status_resume,
max(y.seq) as seq2,
max(y.extern_order_status) as extern
from t_order_demand x 
JOIN t_order_log y ON x.order_id=y.order_id
JOIN p_catalog_status z ON z.status_code_sc=y.extern_order_status
and x.order_id like '%1256%'
group by x.order_id, z.status_resume;

and this is the result:
order id     status_resume       seq    extern
1256         proccess                    2       4
1256        registered                   1        2
1256        pre registered            0        1

i want the result just status resume based on max value from seq and extern. how can i do it? help me.. thanks.
order id     status_resume       seq    extern
1256         proccess                    2       4

Upvotes: 0

Views: 1162

Answers (3)

Zsolt Botykai
Zsolt Botykai

Reputation: 51613

WITH t AS
 (SELECT x.order_id
        ,z.status_resume
        ,MAX(y.seq) AS seq2
        ,MAX(y.extern_order_status) AS extern
  FROM   t_order_demand x
  JOIN   t_order_log y
  ON     x.order_id = y.order_id
  JOIN   p_catalog_status z
  ON     z.status_code_sc = y.extern_order_status
  AND    x.order_id LIKE '%1256%'
  GROUP  BY x.order_id
           ,z.status_resume)
SELECT *
FROM   t
WHERE  (t.seq || t.extern) = (SELECT MAX(tt.seq || tt.extern) FROM t tt);

Might work for you.

Upvotes: 1

Multisync
Multisync

Reputation: 8797

You can use analytic function RANK:

select order_id, status_resume, seq2, extern 
from (
    select x.order_id, z.status_resume,
           max(y.seq) as seq2,
           max(y.extern_order_status) as extern,
           rank() over(partition by x.order_id, z.status_resume order by max(y.seq) desc, max(y.extern_order_status) desc) rnk
    from t_order_demand x 
    JOIN t_order_log y ON x.order_id=y.order_id
    JOIN p_catalog_status z ON z.status_code_sc=y.extern_order_status
    and x.order_id like '%1256%'
    group by x.order_id, z.status_resume
) where rnk = 1;

But it's not clear what do you mean by max of two fields. There sum? The query above retrive the rows with max seq and if several rows have the same seq then only rows with max extern_order_status are retrieved.

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

WITH data AS 
( 
         SELECT   x.order_id, 
                  z.status_resume, 
                  Max(y.seq)                 AS seq2, 
                  Max(y.extern_order_status) AS extern 
         FROM     t_order_demand x 
         join     t_order_log y 
         ON       x.order_id=y.order_id 
         join     p_catalog_status z 
         ON       z.status_code_sc=y.extern_order_status 
         AND      x.order_id LIKE '%1256%' 
         GROUP BY x.order_id, 
                  z.status_resume ) 
SELECT * 
FROM   data 
WHERE  seq || extern = 
(select max(seq || extern) 
FROM   data)
/

A simple test case to validate :

SQL> WITH DATA AS(
  2  SELECT 1 col1, 2 col2 FROM dual UNION ALL
  3  SELECT 5, 7 FROM dual
  4  )
  5  SELECT * FROM DATA
  6  where col1||col2 = (select max(col1||col2) from data)
  7  /

      COL1       COL2
---------- ----------
         5          7

SQL>

Upvotes: 0

Related Questions