I'm trying to figure out a way to return results by using the group by function.

Is it possible to have a group by ignoring the NULL field. So that it does not group NULLs together because I still need all the rows where the specified field is NULL.

my records example TABLEX

i want to group last records with not null columns.

i tried this query but it is not success:

SELECT id, status, mode, update_date FROM TABLEX ORDER BY update_date GROUP BY id

i want to get this result:

how can i solve this situation.

CodePudding user response：

NOTES:

• This is running on oracle 18c.
• There's GOT to be a better way.... and this isn't tested w/ all edge cases I could think of... But it appears to be working....

DEMO: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=be87b5778616a4732db3a3c4787804e0

WITH CTE AS (SELECT 1 ID, null STATUS,0 "MODE", '13:46' UPDATE_DATE from dual UNION ALL
         SELECT 1,null,1,'12:22' from dual UNION ALL
         SELECT 1,1,null,'15:00' from dual UNION ALL
         SELECT 1,0,null,'15:55' from dual)


SELECT A.ID, Z.Status, Y."MODE", max(A.Update_Date) UD
FROM CTE A
CROSS APPLY (SELECT B.Status FROM CTE B WHERE B.ID = A.ID and B.Status is not null ORDER BY UPDATE_DATE DESC FETCH FIRST 1 ROW ONLY) Z
CROSS APPLY (SELECT B."MODE" FROM CTE B WHERE B.ID = A.ID and B."MODE" is not null ORDER BY UPDATE_DATE DESC FETCH FIRST 1 ROW ONLY) Y
GROUP BY ID, Z.Status, Y."MODE"


 ---- -------- ------ ------- 
| ID | STATUS | MODE |  UD   |
 ---- -------- ------ ------- 
|  1 |      0 |    0 | 15:55 |
 ---- -------- ------ ------- 

• I didn't bother with data type casting for the update date so long as the time is in a 24 hour format the results would be the same so I didn't take the time.
• So cross apply is running the inline queries once for each row in the main table and obtaining the status or mode following the rule of not null value and must have the highest Update_Date. I thought First_Value analytic would work; but I couldn't get it working... (though I was using 11 at the time)
• Since it will return the same values for all rows within an ID, grouping by it allows us to consolidate back into 1 record.
• first_value may work on 18 I didn't try it there...

More testing: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=705747c5b5f0e8414a2561fa82842fb7

CodePudding user response：

Here is a complete script creating the table and returning what you want ( and I believe with the logic that you want):

CREATE TABLE tablex (
    id int,
    status int,
    mode int,
    update_date time);
INSERT INTO tablex values(1,null,0,'13:46');
INSERT INTO tablex values(1,null,1,'12:22');
INSERT INTO tablex values(1,1,null,'15:00');
INSERT INTO tablex values(1,0,null,'15:55');
select 
    t.id, 
    t.status,
    t.mode, 
    t.update_date 
from 
    tablex t
join 
(   select id , max(update_date) as u
    from tablex group by id) pre on t.id = pre.id
    and t.update_date = pre.u;
drop table tablex;

NB I don't have Oracle but this is working on mySQL.