How to write SQL Query with group by and having

Question

I have this table which has three columns: number, status and unix_time

number,status,Unix_time
01,Y,112
02,Y,112
01,Y,114
03,,114
01,,115
02,Y,115
04,Y,115
04,Y,119
05,Y,119
06,Y,120
05,Y,120
06,,121
05,Y,121

NOTE: actual value of the column unix_time is in millisec since 1970

OUTPUT: all numbers, whose last appearance (based on timestamp), have status 'Y'

number
02
04
05

Kindly help me, how can I write an oracle SQL query

Answer 1

One option is to use row_number (or perhaps rank? Depends on whether there can be duplicates as far as time is concerned) analytic function (to find which row is the last per each num), and then - in main query - return only those rows whose status is Y.

Sample data:

SQL> WITH
  2     test (num, status, time)
  3     AS
  4        (SELECT '01', 'Y', 112 FROM DUAL
  5         UNION ALL
  6         SELECT '02', 'Y', 112 FROM DUAL
  7         UNION ALL
  8         SELECT '01', 'Y', 114 FROM DUAL
  9         UNION ALL
 10         SELECT '03', '', 114 FROM DUAL
 11         UNION ALL
 12         SELECT '01', '', 115 FROM DUAL
 13         UNION ALL
 14         SELECT '02', 'Y', 115 FROM DUAL
 15         UNION ALL
 16         SELECT '04', 'Y', 115 FROM DUAL
 17         UNION ALL
 18         SELECT '04', 'Y', 119 FROM DUAL
 19         UNION ALL
 20         SELECT '05', 'Y', 119 FROM DUAL
 21         UNION ALL
 22         SELECT '06', 'Y', 120 FROM DUAL
 23         UNION ALL
 24         SELECT '05', 'Y', 120 FROM DUAL
 25         UNION ALL
 26         SELECT '06', '', 121 FROM DUAL
 27         UNION ALL
 28         SELECT '05', 'Y', 121 FROM DUAL),

Query begins here:

 29     temp
 30     AS
 31        (SELECT num,
 32                status,
 33                time,
 34                ROW_NUMBER () OVER (PARTITION BY num ORDER BY time DESC) rn
 35           FROM test)
 36  SELECT num
 37    FROM temp
 38   WHERE     rn = 1
 39         AND status = 'Y';

NU
--
02
04
05

SQL>