Clash Royale CLAN TAG

SQL join most recent results in GROUP BY

I have the following table:

+--------------------+----------+--------+-----------------+
|JOB_RUN_DATE_TIME_ts|JOB_STATUS|JOB_NAME|UPSTREAM_JOB_NAME|
+--------------------+----------+--------+-----------------+
| 1| 0| Y| Z|
| 2| 0| Y| Z|
| 3| 1| Y| Z|
| 4| 0| Y| Z|
| 5| 1| Y| Z|
| 6| 0| Y| Z|
| 1| 0| Z| null|
| 2| 0| Z| null|
| 1| 1| X| Y|
| 2| 0| X| Y|
| 3| 0| X| Y|
| 4| 0| X| Y|
| 5| 0| X| Y|
| 6| 0| X| Y|
| 7| 1| X| Y|
| 8| 1| X| Y|
+--------------------+----------+--------+-----------------+


JOB_NAME is the job for which JOB_STATUS is defined. A job fails if JOB_STATUS = 1. One of my main hypotheses with these data is that an upstream job's failure spells bad news for downstream jobs. I am having troubling creating the table that will allow me to test this hypothesis.

JOB_NAME

JOB_STATUS

JOB_STATUS = 1

Each row in the table represents a particular run for a particular job. For each of these rows, I want to know what the outcome of the last run for its upstream job was. Ultimately, my table would look like this:

+--------------------+----------+--------+-----------------+-----------------+
|JOB_RUN_DATE_TIME_ts|JOB_STATUS|JOB_NAME|UPSTREAM_JOB_NAME|PREV_US_JOB_STATUS
+--------------------+----------+--------+-----------------+-----------------+
| 1| 0| Y| Z| 0|
| 2| 0| Y| Z| 0|
| 3| 1| Y| Z| 0|
| 4| 0| Y| Z| 0|
| 5| 1| Y| Z| 0|
| 6| 0| Y| Z| 0|
| 1| 0| Z| null| null|
| 2| 0| Z| null| null|
| 1| 1| X| Y| 0|
| 2| 0| X| Y| 0|
| 3| 0| X| Y| 1|
| 4| 0| X| Y| 0|
| 5| 0| X| Y| 1|
| 6| 0| X| Y| 0|
| 7| 1| X| Y| 0|
| 8| 1| X| Y| 0|
+--------------------+----------+--------+-----------------+-----------------+


Notice that nulls are populated for rows without upstream jobs. How can I create this view using SQL?

null

I know that using a partition will help to find the most recent record for each job, but I can't figure out the full recipe:

SELECT
*,
ROW_NUMBER() OVER (PARTITION BY UPSTREAM_JOB_NAME ORDER BY JOB_RUN_DATE_TIME_ts DESC) AS seqnum
FROM
tb
LEFT JOIN tb tmp_tb
ON tmp_tb.UPSTREAM_JOB_NAME = tb.JOB_NAME


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.