CODEWITHSUNDEEP
sqlsql-server
pocksoman
pocksoman

Reputation: 11

How to get first and last record from same group in SQL Server?

I'm a new SQL user and need help.

Let's say I have a vehicle number 123 and I've traveled from Region 3 to final destination Region 4. In between, I've visited Region 1 and 5 as well but that's not my concern. Simple example would be as follow.

Original Table

Original Table

Desired Output

Desired Output

How can this be done in SQL query?

Upvotes: 0

Views: 785

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You have a sequence number so you can use some form of aggregation. One method is:

select records,
       max(case when sequence = 1 then fromregion end) as fromregion,
       max(case when sequence = maxsequence then toregion) as toregion
from (select t.*, max(sequence) over (partition by records) as max_sequence
      from t
     ) t
group by records;

Unfortunately, SQL Server doesn't offer "first()" or "last()" as aggregation functions. But it does support first_value() as a window function. This allows you to do the logic without a subquery:

select distinct records,
       first_value(fromRegion) over (partition by records order by sequence) as fromregion,
       first_value(toRegion) over (partition by records order by sequence desc) as toregion
from t;

Upvotes: 2

Related Questions