Getting data from first and last row of each group

Question

I've found many similar topics to this but none I can understand well enough to solve my specific case.

A have a table with the following basic structure:

+------------------------+
| id | session ID | bal  |
+------------------------+
| 0  | 00000002 | 100    |
| 1  | 00000002 | 120    |
| 2  | 00000002 | 140    |
| 3  | 00000001 | 900    |
| 4  | 00000001 | 800    |
| 5  | 00000001 | 500    |
+------------------------+ 

I need to create a (Microsoft SQL) query which returns each unique sessionID along with the first ("start") and last ("end") bal entries based on sequencial value of the ID column. The result would look like this:

+---------------------------+
| session ID | start | end  |
+---------------------------+
| 00000002   | 100   | 140  |
| 00000001   | 900   | 500  |
+---------------------------+

How can I achieve this?

Answer 1

EDIT In reply to your comment, SQL Server supports window functions. One way to look up the first and last bal values per Session ID is:

select  distinct [Session ID]
,       first_value(bal) over (partition by [Session ID] order by id) as [start]
,       first_value(bal) over (partition by [Session ID] order by id desc) as [end]
from    Table1

Example at SQL Fiddle.

Another way (there are many) is increasing and decreasing row numbers:

select  [Session ID]
,       max(case when rn1 = 1 then bal end) as [start]
,       max(case when rn2 = 1 then bal end) as [end]
from    (
        select  row_number() over (partition by [Session ID] order by id) as rn1
        ,       row_number() over (partition by [Session ID] order by id desc) as rn2
        ,       *
        from    Table1
        ) as SubQueryAlias
group by
        [Session ID]

Example at SQL Fiddle.

Answer 2

You can use JOIN and Common Table Expression for readability:

with CTE as
(
    select 
        sessionId, min(id) as firstId, max(id) as lastId
    from 
        log
    group by sessionId
)
select
    CTE.sessionId, Log1.bal as start, Log2.bal as [end]
from
    CTE
    join Log as Log1 on Log1.id = CTE.firstId
    join Log as Log2 on Log2.id = CTE.lastId

See the SQL Fiddle.