How to Group consecutive records to fetch a record based on MAX and MIN of corresponding column?-CodePudding

I have a database with different type of item types, consecutive HISTORY_ID, old and new values, field caption I'd like to filter and the date of change (can be not consecutive) enter image description here

Tried to get the records using ROW_NUMBER() OVER(PARTITION BY

I'd like to get something like enter image description here

CodePudding user response：

Try the following:

select [item id],[item type id]
    ,grouped_old_value=first_value([Old Value]) over (partion by [item id],[item type id] order by History_ID desc)
    ,grouped_new_value=first_value([New Value]) over (partion by [item id],[item type id] order by History_ID  asc)
from yourtable

CodePudding user response：

I'm not sure if I understand you question completely but I will give it a shot anyway.

I think you are trying to get the oldest and newest values of an item based on the time.

In that you want to find the value of old_value from your earliest record and the value of new_value from your latest record.

to select the oldest value you need to do group by item and item_type and get the first item when ordered by date (Or ID if those are incrementing over time) descending. .

to select the newest value you need to do group by item and item_type and get the first item when ordered by date (Or ID if those are incrementing over time). ascending.

that would result in:

select 
[item id]
,[item type id]
,oldest_value = first_value([Old Value]) over (partition by [item id],[item type id] order by [History_ID] desc)
,newest_value = first_value([New Value]) over (partition by [item id],[item type id] order by [History_ID] asc)
from table_name