0

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?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Azim Kamal
  • 105
  • 2
  • 2
  • 5
  • You say of "each phase", but then why does `Phase 1` and `Phase 2` appear twice? – Thom A Dec 06 '21 at 12:59
  • Please share what have you tried so far. Also please do not post code and sample data as image. Help us to help you. Please read [ask] and [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) in [help] – Pred Dec 06 '21 at 13:00
  • Sorry for the confusion. Since the data is continuous and in sequence, I want it to select only last row. Then same phase will come again. So that's why it will appear more than one – Azim Kamal Dec 06 '21 at 13:02
  • What you have here is a gaps and island problem then. – Thom A Dec 06 '21 at 13:08
  • 1
    Your records #2 and #3 have EXACTLY the same Timestamp and Phase. This means there is no truly last record for the first group and you could get a Greentime value of 4 or 7 depending on how SQL Server decides to run your query. Is this real, or did you make up this data and put identical timestamps by accident? – Chris Maurer Dec 06 '21 at 13:46

1 Answers1

3

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;
Charlieface
  • 52,284
  • 6
  • 19
  • 43