CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
Azim Kamal
Azim Kamal

Reputation: 143

SQL Take Last Row Based on Group

I have this kind of SQL table with different timestamp

enter image description here

I want to query it so it will take only the last row of each Phase. The output should be like this:

enter image description here

Can someone help?

Upvotes: 0

Views: 85

Answers (1)

Charlieface
Charlieface

Reputation: 71668

You can use LEAD to check the next value

WITH NextValues AS (
    SELECT *,
      IsEnd = CASE WHEN LEAD(Phase) OVER (ORDER BY Timestamp) = Phase THEN 0 ELSE 1 END
    FROM YourTable
)
SELECT
  Timestamp,
  Phase,
  greentime
FROM NextValues t
WHERE IsEnd = 1;

Upvotes: 3

Related Questions