You don't need any (inefficient) self-joins. Just use aggregation with KEEP DENSE_RANK FIRST|LAST:

SELECT reservation,
       MIN( prefix      ) KEEP ( DENSE_RANK FIRST ORDER BY date_flight ) AS prefix_min,
       MIN( flight      ) KEEP ( DENSE_RANK FIRST ORDER BY date_flight ) AS flight_min,
       MIN( origin      ) KEEP ( DENSE_RANK FIRST ORDER BY date_flight ) AS origin_min,
       MIN( destination ) KEEP ( DENSE_RANK FIRST ORDER BY date_flight ) AS desination_min,
       MIN( date_flight ) AS date_flight_min,
       MIN( prefix      ) KEEP ( DENSE_RANK LAST ORDER BY date_flight ) AS prefix_max,
       MIN( flight      ) KEEP ( DENSE_RANK LAST ORDER BY date_flight ) AS flight_max,
       MIN( origin      ) KEEP ( DENSE_RANK LAST ORDER BY date_flight ) AS origin_max,
       MIN( destination ) KEEP ( DENSE_RANK LAST ORDER BY date_flight ) AS desination_max,
       MAX( date_flight ) AS date_flight_max
FROM   table_name
GROUP BY reservation;

so for your test data:

create table table_name ( RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT ) AS
SELECT 111, 'LA', 123, 'LAX', 'MIA', DATE '2020-02-01' + INTERVAL '00:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 111, 'LA', 122, 'MIA', 'SCL', DATE '2020-02-01' + INTERVAL '10:30' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 111, 'LA', 667, 'MIA', 'SCL', DATE '2020-02-03' + INTERVAL '14:15' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 111, 'LA', 882, 'SCL', 'ARG', DATE '2020-02-03' + INTERVAL '16:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 111, 'LA', 111, 'SCL', 'ARG', DATE '2020-02-03' + INTERVAL '23:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 111, 'LA', 966, 'SCL', 'ARG', DATE '2020-02-03' + INTERVAL '23:30' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 111, 'LA', 622, 'SCL', 'ARG', DATE '2020-02-05' + INTERVAL '08:00' HOUR TO MINUTE FROM DUAL;

this outputs:

RESERVATION | PREFIX_MIN | FLIGHT_MIN | ORIGIN_MIN | DESINATION_MIN | DATE_FLIGHT_MIN     | PREFIX_MAX | FLIGHT_MAX | ORIGIN_MAX | DESINATION_MAX | DATE_FLIGHT_MAX    
----------: | :--------- | ---------: | :--------- | :------------- | :------------------ | :--------- | ---------: | :--------- | :------------- | :------------------
        111 | LA         |        123 | LAX        | MIA            | 2020-02-01 00:00:00 | LA         |        622 | SCL        | ARG            | 2020-02-05 08:00:00

db<>fiddle here

Having Sums, Averages, and Other Grouped Data, All aggregate functions group data to ultimately produce a single result value. The query requests a count of all rows and a count of all manager values for all employee Code Listing 9: MAX and MIN obtain maximum and minimum column values Just as a SELECT list can use a WHERE clause to filter the result set to� SQL> select ename 2 , deptno 3 , sal 4 from ( 5 select ename 6 , deptno 7 , sal 8 , max (sal) over (partition by deptno) max_sal 9 , min (sal) over (partition by deptno) min_sal 10 from emp 11 ) 12 where sal = max_sal 13 or sal = min_sal 14 order by deptno, sal 15 / ENAME DEPTNO SAL ----- ----- ----- KISHORE 10 1300 SCHNEIDER 10 5000 CLARKE 20

You can group by reservation to get the earliest and the latest date and then join this query to 2 copies of the table:

select 
  t.RESERVATION,
  t1.PREFIX PREFIX_min, 
  t1.FLIGHT FLIGHT_min, 
  t1.ORIGIN ORIGIN_min, 
  t1.DESTINATION DESTINATION_min, 
  t1.DATE_FLIGHT DATE_FLIGHT_min,
  t2.PREFIX PREFIX_max, 
  t2.FLIGHT FLIGHT_max, 
  t2.ORIGIN ORIGIN_max, 
  t2.DESTINATION DESTINATION_max, 
  t2.DATE_FLIGHT DATE_FLIGHT_max
from (
  select 
    RESERVATION,
    min(DATE_FLIGHT) DATE_FLIGHT_min, 
    max(DATE_FLIGHT) DATE_FLIGHT_max
  from tablename
  group by RESERVATION
) t
inner join tablename t1 on t1.RESERVATION = t.RESERVATION AND t1.DATE_FLIGHT = t.DATE_FLIGHT_min
inner join tablename t2 on t2.RESERVATION = t.RESERVATION AND t2.DATE_FLIGHT = t.DATE_FLIGHT_max

This will return 1 row for each reservation if there are no duplicate dates. See the demo. Results:

> RESERVATION | PREFIX_MIN | FLIGHT_MIN | ORIGIN_MIN | DESTINATION_MIN | DATE_FLIGHT_MIN  | PREFIX_MAX | FLIGHT_MAX | ORIGIN_MAX | DESTINATION_MAX | DATE_FLIGHT_MAX  
> ----------: | :--------- | ---------: | :--------- | :-------------- | :--------------- | :--------- | ---------: | :--------- | :-------------- | :---------------
>         111 | LA         |        123 | LAX        | MIA             | 2020-02-01 00:00 | LA         |        622 | SCL        | ARG             | 2020-02-05 08:00

Tutorial: Aggregating Rows: Databases for , Aggregate Functions � Sum: the result of adding up all the values for the expression in the group � Min: the smallest value in the group � Max: the� If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.

Bit similar to @forpas but using CTE

with min_max
as
(
select 
distinct reservation,
min(date_flight) over(partition by reservation) as date_flight_min,
max(date_flight) over(partition by reservation) as date_flight_max
from tablename 
)
select
m.reservation,
t1.prefix as prefix_min,
t1.flight as flight_min,
t1.origin as origin_min,
t1.destination as destination_min,
m.date_flight_min,
t2.prefix as prefix_max,
t2.flight as flight_max,
t2.origin as origin_max,
t2.destination as destination_max,
m.date_flight_max
from
min_max m
join tablename t1 on t1.date_flight=m.date_flight_min
join tablename t2 on t2.date_flight=m.date_flight_max

demo

Ask TOM "Howto select first value in a group by bunch of ro", How to remove the rows in a group if any one row in the group does not satisfy a condition? I was not able to filter the group if the status_id is not in 203 or 204. not sure what you mean at all. min, max, sum, avg, count do not always and with repeated data - min, max, sum are definitely NOT the same The query requests a count of all rows and a count of all manager values for all employee records with a hire date matching the current day’s system date, SYSDATE. Because no one was hired on the date the query was run, a count value of 0 is returned. Code Listing 7: COUNT(*) and COUNT(column_name) both return 0 when no rows match

Aggregate Functions, Aggregate functions return a single result row based on groups of rows, rather clause, then Oracle applies aggregate functions in the select list to all the rows in the For all the remaining aggregate functions, if the data set contains no rows, The aggregate functions MIN , MAX , SUM , AVG , COUNT , VARIANCE , and� The MIN function is one that I use quite a lot in Oracle SQL. In this article, I’ll explain how to use it, and show you some examples. Purpose of the Oracle MIN Function. The MIN function returns the minimum value of the expression you provide it. This is almost the same as the lowest value.

The MIN() and MAX() optimizations, The optimizer knows that it can avoid iterating through all the source rows in a result to compute a MIN() or MAX() aggregate when data are already in the right� CASE, GROUP BY, ROW_NUMBER, and WITH are all described in the SQL Language manual. The only one that might be tricky to find in the index is WITH. In your problem, you don't want a separate row of output for each combination of deptno and job; you want a separate row of output for every combination of col1, col2, and col3.

SELECT MIN(MyTable01.Id) as Id, MyTable01.Val as Val, MyTable01.Kind as Kind FROM MyTable MyTable01, (SELECT Val,MIN(Kind) as Kind FROM MyTable GROUP BY Val) MyTableGroup WHERE MyTable01.Val = MyTableGroup.Val AND MyTable01.Kind = MyTableGroup.Kind GROUP BY MyTable01.Val,MyTable01.Kind ORDER BY Id;

Comments

• HOw do you determine that flight 123 is the 1st flight, and not 122? They both have the same date?
• you're right , its date_flight its a time_stamp value , I will edit the post