CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Thomas Schmidt
Thomas Schmidt

Reputation: 1378

SQL group by value depending on an interval given by the min and max of a date field

I have following table given:

----------------------------     
| x    |  y   | date       |
----------------------------
| 1    | 1    | 01.01.2000 |
| 1    | 1    | 02.01.2000 |
| 1    | 1    | 03.01.2000 |
| 1    | 2    | 04.01.2000 |
| 1    | 2    | 05.01.2000 |
| 1    | 2    | 06.01.2000 |
| 1    | 1    | 07.01.2000 |
| 1    | 1    | 08.01.2000 |
| 1    | 1    | 09.01.2000 |
----------------------------

Now i need to group the table depending on both y and x values, depending on the resulting interval given by the date column:

-----------------------------------------     
| x    |  y   |  min       | max        |
-----------------------------------------
| 1    | 1    | 01.01.2000 | 03.01.2000 |
| 1    | 2    | 04.01.2000 | 06.01.2000 |
| 1    | 1    | 07.01.2000 | 09.01.2000 |
-----------------------------------------

Just grouping y will result in a wrong result, since there is the possibility that the y value switches back to a previous state as stated in the example.

Upvotes: 0

Views: 82

Answers (1)

Serg
Serg

Reputation: 22811

Try 

select x,y, min(dat), max(dat)
from (
    select x,y, dat, row_number() over(order by dat) - row_number() over(partition by x, y order by dat) as grp
    from mytable 
)
group by x,y, grp
order by  min(dat), x,y

This is an old trick, row_number( ..)-row_number(partition..) keeps the same value till partitioned data do not change and changes the value when x,y change. So with x,y this computed grp identifies every group of the same x,y.

Upvotes: 3

Related Questions