...

Get the First & Last Value in a Group

 
SQL(179)

【Question】

I have a table sorted by date:
SN    Time
1   2015-05-19 12:00:00
1   2015-05-19 12:00:01
1   2015-05-19 12:00:02
2   2015-05-19 12:00:03
2   2015-05-19 12:00:04
2   2015-05-19 12:00:05
1   2015-05-19 12:00:06
1   2015-05-19 12:00:07
1   2015-05-19 12:00:08
Below is the desired result. Is there any idea about how to get it? In the sorted data, SN values are appear alternatively. Each change will be displayed.
SN   Start              End
1   2015-05-19 12:00:00   2015-05-19 12:00:02
2   2015-05-19 12:00:03   2015-05-19 12:00:05
1   2015-05-19 12:00:06   2015-05-19 12:00:08

 

【Answer】

To group the rows, you need to write a subquery to create group numbers first. It’s simple to do it in SPL (Structured Process Language). The following SPL script is easy to understand:

A

1

$SELECT SN,Time FROM TB   ORDER BY Time

2

=A1.group@o(SN:SN;~(1).Time:Start,~.m(-1).Time:End)

A1: Retrieve data and sorted it by Time in SQL;

A2: Compare an SN value with that in the next row to put the two rows in same group if the values are same; for each group, get Time from the first row to make it Star field value and Time from the last row to make it End field value.


SQL(179)
 
 •  322 View  •