SQL nested query

Question

I have a table with the attributes PARAMETER_ID, Value and Time and I want to have each PARAMETER_ID with its min(Value) and the Time when the Value is minimum and its max(Value) and the Time when the Value is maximum, could you please tell me the query? thanks alot

Answer 1

At the link Max posted, scroll down to Mancaus's answer, which is the best. Of course, this assumes the dialect of SQL you're using supports ROW_NUMBER() and RANK().

As for your specific, question, it's very similar to this question. Here's a solution (untested, given that you didn't provide any CREATE TABLE or INSERT statements with sample data). I left out the final pivot to get the min/max values and times into the same row for each PARAMETER_ID. Also, if the max or min Value occurs twice at different times for a given PARAMETER_ID, this will give you the most recent occurrence.

with TRanked(PARAMETER_ID,Value,Time,upRank,downRank) as (
  select PARAMETER_ID,Value,Time,
    row_number() over (
      partition by PARAMETER_ID
      order by Value, Time desc
    ),    
    row_number() over (
      partition by PARAMETER_ID
      order by Value desc, Time desc
    )
  from T
), T_extremes(PARAMETER_ID,tag,Value,Time) as (
  select
    PARAMETER_ID, 'min', Value, Time from TRanked where upRank = 1
    union all
    PARAMETER_ID, 'max', Value, Time from TRanked where downRank = 1
)
  select * from T_extremes;

The notes I made in the other thread apply here also.