0

I have 2 tables. In one I have information for all jobs in db in other I have each job version after execution. How can I take only the row where is the last version of the job executed:

SELECT ROW_NUMBER() OVER(PARTITION BY j.job_id ORDER BY j.job_id ASC ) AS rownumber,
       jv.*,
       j.*
FROM   jobs_internal.job_versions jv
  JOIN jobs_internal.jobs j ON jv.job_id = j.job_id

rownumber   job_id  job_version_number  job_id  name    delete_requested_time   is_system   schedule_start_time schedule_end_time   schedule_interval_type  schedule_interval_count enabled description last_job_execution_id
123 522C48C9-198F-40F8-BB30-16556C55DAC7    123 522C48C9-198F-40F8-BB30-16556C55DAC7    run_everywhere  NULL    0   0001-01-01 00:00:00.0000000 9999-12-31 11:59:59.0000000 Once    1   0   Execute Statement on ALl DBs    NULL
124 522C48C9-198F-40F8-BB30-16556C55DAC7    124 522C48C9-198F-40F8-BB30-16556C55DAC7    run_everywhere  NULL    0   0001-01-01 00:00:00.0000000 9999-12-31 11:59:59.0000000 Once    1   0   Execute Statement on ALl DBs    NULL
125 522C48C9-198F-40F8-BB30-16556C55DAC7    125 522C48C9-198F-40F8-BB30-16556C55DAC7    run_everywhere  NULL    0   0001-01-01 00:00:00.0000000 9999-12-31 11:59:59.0000000 Once    1   0   Execute Statement on ALl DBs    NULL
1   44228D08-5AD4-4B74-A200-6BCCC10E6B97    1   44228D08-5AD4-4B74-A200-6BCCC10E6B97    run_Partial_Bulkload    NULL    0   0001-01-01 00:00:00.0000000 9999-12-31 11:59:59.0000000 Once    1   0   Execute Partial Bulkload in Remote DB   NULL
2   44228D08-5AD4-4B74-A200-6BCCC10E6B97    2   44228D08-5AD4-4B74-A200-6BCCC10E6B97    run_Partial_Bulkload    NULL    0   0001-01-01 00:00:00.0000000 9999-12-31 11:59:59.0000000 Once    1   0   Execute Partial Bulkload in Remote DB   NULL
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Boyan Mihnev
  • 23
  • 5
  • 1
    MAX(j.job_id) ....? – Mitch Wheat Sep 21 '22 at 14:31
  • Put it in a subquery then do `where rownumber = 1` also you want `desc` not `asc` – Charlieface Sep 21 '22 at 14:48
  • @Charlieface ``` select * from ( SELECT ROW_NUMBER() OVER(PARTITION BY j.job_id ORDER BY jv.job_version_number desc ) AS rownumber, jv.*, j.* FROM jobs_internal.job_versions jv JOIN jobs_internal.jobs j ON jv.job_id = j.job_id ) as q where rownumber =1 ``` returns : Msg 8156, Level 16, State 1, Line 122 The column 'job_id' was specified multiple times for 'q'. – Boyan Mihnev Sep 21 '22 at 14:52
  • 1
    Because you used `*` @BoyanMihnev . *Define* the columns you need. – Thom A Sep 21 '22 at 14:55

0 Answers0