Should become:

Where only the rows where day_id is the maximum value is kept.

I'm using Oracle SQL developer.

CodePudding user response：

This can work:

SELECT job_id, day_id
  FROM some_table tbl
 WHERE (job_id, day_id) IN (SELECT job_id, MAX(day_id)
                              FROM some_table
                             GROUP BY job_id);

CodePudding user response：

You probably already figured out how to get the maximum value per JOB_ID like this:

WITH BASIC_AGGS AS (
SELECT
    JOB_ID,
    MAX(DAY_ID) as DAY_ID_MAX
FROM mytable
GROUP BY JOB_ID)

SELECT BASIC_AGGS.JOB_ID, BASIC_AGGS.DAY_ID_MAX
FROM BASIC_AGGS

But it sounds like you want to join this back to your original table in order to keep all the records where the maximum amount matches the row. Like this:

WITH BASIC_AGGS AS (
SELECT
    JOB_ID,
    MAX(DAY_ID) as DAY_ID_MAX
FROM mytable
GROUP BY JOB_ID)

SELECT BASIC_AGGS.JOB_ID, BASIC_AGGS.DAY_ID_MAX
FROM BASIC_AGGS
INNER JOIN mytable
ON BASIC_AGGS.JOB_ID = mytable.JOB_ID
AND BASIC_AGGS.JOB_ID_MAX = mytable.DAY_ID

I'm not familiar with Oracle, but if it doesn't support using this style of SQL called CTE, you can write it in a different style that nests the first query as a subquery like this:

SELECT *
FROM mytable
INNER JOIN (SELECT JOB_ID, MAX(DAY_ID) as DAY_ID_MAX 
    FROM mytable 
    GROUP BY JOB_ID
   ) as rsMAX
ON mytable.JOB_ID = rsMAX.JOB_ID
AND mytable.DAY_ID = rsMAX.DAY_ID_MAX

CodePudding user response：

SELECT X.JOB_ID,X.DAY_ID   FROM
(
  SELECT C.JOB_ID,C.DAY_ID,
   RANK()OVER(PARTITION BY C.JOB_ID ORDER BY C.DAY_ID DESC)XCOL
   FROM TABLE_DATA AS C
)X WHERE X.XCOL=1
ORDER BY X.JOB_ID