First and last row of consecutive rows

[this.wi...@gmail.com]

Hi everyone

I am looking for a way to get the the first and the last row from consecutive rows which have the same value in specific columns:

The data is defined as follows:

CREATE TABLE IF NOT EXISTS t
(
device String,
timestamp UInt64,
key String
)
ENGINE = ReplacingMergeTree()
ORDER BY (device, timestamp)

now i'd like to get the first and the last row where device and key are the same for consecutive rows.

I'd be happy to use aggregation but I don't see how to achive this.

Anyone has an idea?

Cheers This

[Amit Sharma]

When you say first and last row where device and key are same, I am assuming you are looking for latest and earliest record for a device,key. You can get min and max timestamp values like this. 

select device, key, min(timestamp), max(timestamp) from t group by device, key;

Hope this helps