What I want to do is as follows:

1. Get min(Date) and max(Date) for each ID
2. Get result values from the date we got at step (1)
3. Check the value has increased or decreased

Is it possible to index in SQL?

Expected result:

I can retrieve step (1) but stuck from the step (2).

CodePudding user response：

I am not sure about structure of your result set...you can try this and change accordingly.

with t as(
    SELECT ID, MIN(Date)min, MAX(Date)max
    FROM Temp
    GROUP BY ID
    )
SELECT t.ID, t.min , t.max, min.Result min_result, max.Result max_result, CASE WHEN min.Result<max.Result  THEN 'Increased' ELSE 'Decreased' END IsIncreased
FROM t
INNER JOIN Temp min on(min.Id = t.Id and min.Date = t.min)
INNER JOIN Temp max on(max.Id = t.Id and max.Date = t.max)

CodePudding user response：

(Don't name a column "date", that's a SQL keyword. Use something like "created_on", and it's more descriptive.)

Normally you'd use group by to get the min/max.

select id, max([date]), min([date])
from example
group by id

But you also need the result for the min and max rows. This means you can't aggregate, you need the actual min and max rows.

You can get this with the row_number window function to assign a rank to each row per id.

select
  id, [date], result,
  row_number() over (partition by id order by [date] desc) as first,
  row_number() over (partition by id order by [date] asc) as last
  from example

Now we can treat that a table using a Common Table Expression and select only those with first=1 and last=1.

with minmax as (
  select
    id, [date], result,
    row_number() over (partition by id order by [date] desc) as first,
    row_number() over (partition by id order by [date] asc) as last
    from example
)
select
  id, [date], result
from minmax
where first = 1 or last=1;

Demonstration.