How can we display first row and last row of given table using rownum without using union clause

user_14983447 0
Viewed 125

Sample Input

Name | Value | Timestamp
-----|-------|-----------------
One  | 1     | 2016-01-01 02:00
Two  | 3     | 2016-01-01 03:00
One  | 2     | 2016-01-02 02:00
Two  | 4     | 2016-01-03 04:00

Desired Output

Name Value EarliestTimestamp LatestTimestamp
One 2 2016-01-01 02:00 2016-01-02 02:00
Two 4 2016-01-01 03:00 2016-01-03 04:00

Instead of using union clause to display the first and last row. How can we display it?

SELECT * FROM TABLE 
WHERE ROWNUM = 1
UNION ALL
SELECT * FROM TABLE 
WHERE ROWNUM IN (SELECT MAX(ROWNUM) FROM TABLE);

Please let me the solution for this.Thanks in advance.

aggregate-functions oracle sql
1 Answers

You seem to be describing aggregation:

select name, max(value), min(timestamp), max(timestamp)
from t
group by name;

Your question suggests that you might want the latest value instead of the maximum. If so, use the first aggregate function:

select name,
       max(value) keep (dense_rank first order by timestamp desc),
       min(timestamp), max(timestamp)
from t
group by name;

Documentation for first: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/FIRST.html#GUID-85AB9246-0E0A-44A1-A7E6-4E57502E9238

user_11440350
Related