How to get MIN and MAX date from table data

We have a nightly job that uses sql loader to insert data from a csv file. Here is a small sample of the data:

``````select * from ktimov.press_schd order by job;

PLANT JOB EST_COMPLETION INSERT_DATE
10    111 6/26/2017      5/10/2017
10    111 7/1/2017       5/11/2017
10    111 7/5/2017       5/12/2017
10    222 6/20/2017      5/10/2017
10    222 6/26/2017      5/11/2017
10    333 7/26/2017      5/10/2017
10    333 6/30/2017      5/11/2017
10    333 8/10/2017      5/12/2017
10    333 8/6/2017       5/13/2017
10    333 8/7/2017       5/14/2017
``````

`INSERT_DATE` is default sysdate of when the record was inserted.
`EST_COMPLETION` is an estimate of when the JOB will be completed. So for example:

On 5/10/17, JOB# 111 was estimated to be completed by 6/26. Then on 5/11 the estimated completion was changed to 7/1.

I'm looking to query this data to show the following:

JOB#, First EST Date, Last Est date, Count of how many times it was changed.

The end result should be like this:

``````JOB FIRST_DATE LAST DATE Count_Changes
111 6/26/2017  7/5/2017  3
222 6/20/2017  6/26/2017 2
333 7/26/2017  8/7/2017  5
``````

Any ideas how I can accomplish this?

Here's a simple method. We use a CTE to get the first and last `INSERT_DATE`s for each job; then, join to the main table to retrieve the estimated completions dates from those first and last loads:

``````WITH JobFirstLast (job, first_load, last_load, load_count) AS
(SELECT job,
FROM ktimov.press_schd
GROUP BY job
)
SELECT j.job
,f.EST_COMPLETION as first_est_completion
,l.EST_COMPLETION as last_est_completion
FROM JobFirstLast j
LEFT  JOIN ktimov.press_schd f ON (j.job = f.job AND j.first_load = f.INSERT_DATE)
LEFT  JOIN ktimov.press_schd l ON (j.job = l.job AND j.last_load = l.INSERT_DATE);
``````

NOTE: I'm assuming that the first load is simply establishing the initial values, and that only subsequent loads are counted as changes. If you really want the total number of loads, change `j.load_count - 1` to `j.load_count`.

You can use `FIRST` and `LAST` along with `KEEP`:

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

One implementation is as follows:

``````SELECT JOB
, MIN(EST_COMPLETION) KEEP (DENSE_RANK FIRST ORDER BY INSERT_DATE)
, MIN(EST_COMPLETION) KEEP (DENSE_RANK LAST ORDER BY INSERT_DATE)
, COUNT(*)
FROM press_schd
GROUP BY JOB;
``````

Result set:

Note that the `MIN` aggregate function serves no purpose here because you will always get at most one row per `INSERT_DATE` for each `JOB`. It's there just so the query will compile.

``````SELECT job,