Getting data from the first and last row of each group

Question

I have found many similar topics, but I cannot 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 query (Microsoft SQL) that returns each unique sessionID along with the first ("start") and last ("end") score based on the sequential value of the ID column. The result will look like this:

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

How can i achieve this?

Answer 1

EDIT . In response to your comment, SQL Server supports window functions. One way to find the first and last bal values ​​for Session ID :

 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 in SQL Fiddle.

Another way (there are many) is to increase and decrease line 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 in SQL Fiddle.

Answer 2

You can use JOIN and Common Table Expression to read:

 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 SQL Fiddle .

Answer 3

I assume that bal is numeric (although in this case this is not necessary since all entries are 3 in length)

 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

Answer 4

In MySQL, it could be something 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` ); 

Answer 5

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