Oppolzer - Informatik / Blog

select t1.x, t2.x
from tablex t1, tablex t2
where t2.x = (select min (x)
from tablex
where x > t1.x)
union all
select max (x), 99999
from tablex

should do the same and should be less expensive,
esspecially if x is at the left side of an index,
then the whole thing will be index-only, I think.

Kind regards

Bernd


Am 11.09.2011 11:33, schrieb Bernd Oppolzer:
> couldn't try it, but maybe this will work:
>
> select t1.x, coalesce (min(t2.x), 99999)
> from tablex t1
> left join
> tablex t2
> on t1.x < t2.x2
> group by t1.x;
>
> Maybe a little expensive; maybe there is another solution
> using a correlated subquery. Every solution will have to take
> care about the situation where no bigger value is found.
>
> Kind regards
>
> Bernd
>
>