Forum Stats

  • 3,740,478 Users
  • 2,248,262 Discussions
  • 7,861,265 Comments

Discussions

filter date and values max()

User_AC3QE
User_AC3QE Member Posts: 34 Red Ribbon
edited Mar 18, 2021 12:06AM in SQL & PL/SQL
Hello
I need to filter the fields dt_liberacao by the biggest date and the value (peso_pre, peso_pos e peso_pos_ult)

if the value of the largest date is null, you need to get the second largest.


exempl:


it is necessary that only the values ​​peso_pre 78.15, peso_pos 76.45 and peso_pos_ult 76.9 remain on the line

CREATE TABLE ktv_hd (

DT_LIBERACAO DATE ,

PESO_PRE VARCHAR2(30) ,

PESO_POS VARCHAR2(20) ,

PESO_POS_ULT VARCHAR2(20) 

/

INSERT INTO ktv_hd VALUES (TO_DATE('03/02/2021 20:51:46','DD/MM/YYYY HH24:MI:SS'),NULL,'76,45','76,9');

INSERT INTO ktv_hd VALUES (TO_DATE('03/02/2021 19:37:19','DD/MM/YYYY HH24:MI:SS'),NULL,'76,9',NULL);

INSERT INTO ktv_hd VALUES (TO_DATE('03/02/2021 16:30:50','DD/MM/YYYY HH24:MI:SS'),'78,15','76,9',NULL);

INSERT INTO ktv_hd VALUES (TO_DATE('01/02/2021 21:25:50','DD/MM/YYYY HH24:MI:SS'),NULL,'76,9','77');

INSERT INTO ktv_hd VALUES (TO_DATE('01/02/2021 17:21:58','DD/MM/YYYY HH24:MI:SS'),'79',NULL,NULL);

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,700 Red Diamond
    edited Mar 18, 2021 12:31AM Accepted Answer

    Hi,

    Thanks for including the sample data; that's very helpful. Don't forget to post the exact results you want from that sample data.

    Do you want only one row of output? Here's one way to do that:

    SELECT  MIN (peso_pre)    KEEP ( DENSE_RANK FIRST
    	   		   	 ORDER BY  NVL2 (peso_pre, 1, 2)
    				 ,   	   dt_liberacao DESC
    			      ) AS last_peso_pre
    ,     MIN (peso_pos)     KEEP ( DENSE_RANK FIRST
    	   		   	ORDER BY  NVL2 (peso_pos, 1, 2)
    				,   	  dt_liberacao DESC
    			      ) AS last_peso_pos
    ,     MIN (peso_pos_ult) KEEP ( DENSE_RANK FIRST
    	   		   	ORDER BY  NVL2 (peso_pos_ult, 1, 2)
    				,   	  dt_liberacao DESC
    			      ) AS last_peso_pos_ult
    FROM	 ktv_hd
    ;
    

    What do you want in case of a tie, where two or more rows have the same dt_liberacao? MIN, above, takes the lowest value from the contenders. I(If dt_liberacao is unique, then it doesn't matter if you use MIN or MAX.)

    Shouldn't peso_pre, peso_pos and peso_pos_ult be NUMBERs?

    User_AC3QE

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,700 Red Diamond
    edited Mar 18, 2021 12:31AM Accepted Answer

    Hi,

    Thanks for including the sample data; that's very helpful. Don't forget to post the exact results you want from that sample data.

    Do you want only one row of output? Here's one way to do that:

    SELECT  MIN (peso_pre)    KEEP ( DENSE_RANK FIRST
    	   		   	 ORDER BY  NVL2 (peso_pre, 1, 2)
    				 ,   	   dt_liberacao DESC
    			      ) AS last_peso_pre
    ,     MIN (peso_pos)     KEEP ( DENSE_RANK FIRST
    	   		   	ORDER BY  NVL2 (peso_pos, 1, 2)
    				,   	  dt_liberacao DESC
    			      ) AS last_peso_pos
    ,     MIN (peso_pos_ult) KEEP ( DENSE_RANK FIRST
    	   		   	ORDER BY  NVL2 (peso_pos_ult, 1, 2)
    				,   	  dt_liberacao DESC
    			      ) AS last_peso_pos_ult
    FROM	 ktv_hd
    ;
    

    What do you want in case of a tie, where two or more rows have the same dt_liberacao? MIN, above, takes the lowest value from the contenders. I(If dt_liberacao is unique, then it doesn't matter if you use MIN or MAX.)

    Shouldn't peso_pre, peso_pos and peso_pos_ult be NUMBERs?

    User_AC3QE
  • mathguy
    mathguy Member Posts: 9,836 Gold Crown

    A bit simpler:

    select min(peso_pre) keep
             (dense_rank last order by nvl2(peso_pre, dt_liberacao, null) nulls first) as peso_pre,
           min(peso_pos) keep
             (dense_rank last order by nvl2(peso_pos, dt_liberacao, null) nulls first) as peso_pos,
           min(peso_pos_ult) keep
             (dense_rank last order by nvl2(peso_pos_ult, dt_liberacao, null) nulls first) as peso_pos_ult
    from  ktv_hd
    ;
    
    User_AC3QE
Sign In or Register to comment.