Ad

How Can I Solve Sql Record Grouping Problem?

- 1 answer

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

idstatusmodeupdate_date
1null013:46
1null112:22
11null15:00
10null15:55

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:

idstatusmodeupdate_date
10015:55

how can i solve this situation.

Ad

Answer

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

Ad
source: stackoverflow.com
Ad