CODEWITHSUNDEEP
oracle-sqldeveloper
Jdbfkfo
Jdbfkfo

Reputation: 29

Oracle SQL Developer how to get Min unix timestamp for max order per line

im running Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 i have a table like the following:

Unix_Timestamp Line Order_Number
1660496421 1 299
1670496421 1 299
1660456421 1 298
1660473051 1 298
1660573526 2 300
1660473044 2 300

Unix_Timestamp is a unique column i want to get the min Unix_Timestamp value for the max Order_Number value per Line. so get the most recent order per line, and get the min Unix_Timestamp for that order_number Order_Number value goes up in ascending value and each number can only belong to one Line.

i do not need the order_number value in my dataset but i need it considered in my script

so far i can get the min Unix_Timestamp value per line but im struggling to factor in the max order_number:

select Line,
Min(Unix_Timestamp) as Unix_Timestamp
from..
join..
where..
group by Line

any help would be appreciated thank you

Upvotes: 0

Views: 75

Answers (1)

Alex Poole
Alex Poole

Reputation: 191465

You can use keep last:

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.

In your case you can do:

select line,
  min(unix_timestamp) keep (dense_rank last order by order_number) as unix_timestamp
from your_table
group by line

LINE UNIX_TIMESTAMP
---- --------------
   1 1660496421
   2 1660473044

db<>fiddle

(The difference between the outputs is hard to spot - 1660496421 looks very similar to 1660456421...)

Upvotes: 1

Related Questions