How do I aggregate over ordered subsets of rows in SQL?

Question

Suppose I have a table t filled like this:

x y z 
- - - 
A 1 A 
A 4 A 
A 6 B 
A 7 A 
B 1 A 
B 2 A 

Basically, columns x and z are random letters and y random numbers.

I want to aggregate the rows as follows:

x z min(y) max(y)
- - ------ ------
A A 1      4
A B 6      6
A A 7      7
B A 1      2

In other words: Given that the rows are ordered by x, y, and z, select the minimum and maximum y for each consecutive group of x and z.

Note that this query is not what I need:

select x, z, min(y), max(y)
from t
group by x, z

As this would result in the following unwanted result:

x z min(y) max(y)
- - ------ ------
A A 1      7
A B 6      6
B A 1      2

So the question is: is there a way to express what I want in SQL?

(In case the solution depends on the SQL dialect: an Oracle solution would be preferred.)

Answer 1

Here a solution, but I don't have the time to explain it step by step:

select x, z, min(y), max(y)
from (
  select b.* , sum(switch) over (order by rn) as grp_new 
  from(
    select a.* , 
           case when grp = (lag(grp) over (order by rn))
           then 0 
           else 1 end as switch
    from 
        (select x,y,z, 
                dense_rank() over (order by x, z) as grp,
                row_number() over (order by x, y, z) rn
         from t
    )a
  )b
)c
group by x, z, grp_new
order by grp_new

SQLFIDDLE to test it.