CODEWITHSUNDEEP
sqlsql-servergroup-by
user2769442
user2769442

Reputation: 123

Getting data from first and last row of each group

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?

Upvotes: 9

Views: 24930

Answers (5)

Md Shahadat Hossain
Md Shahadat Hossain

Reputation: 347

SELECT FIRST(column_name), LAST(column_name) FROM table_name; http://forums.mysql.com/read.php?65,363723,363723

Upvotes: -1

Caffé
Caffé

Reputation: 1171

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.

Upvotes: 4

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 12

Don Del Grande
Don Del Grande

Reputation: 429

I assume bal is numeric (although it doesn't have to be in this case, as all entries have length 3)

select sessionID
     , min(bal) as start
     , max(bal) as end
from table_name
group by sessionID

where "table_name" is the name of your table

Upvotes: 2

Enrique Muñoz Rodas
Enrique Muñoz Rodas

Reputation: 461

In MySQL may be like this:

SELECT `session ID`, MIN(bal) AS start, MAX(bal) AS end FROM `yourtable` WHERE `session ID` IN (
    SELECT DISTINCT(`session ID`) FROM `yourtable`
);

Upvotes: 0

Related Questions