"myname" <myname@ihatespam.com> wrote in message
news:c4r7hl$1dg$1@muguet.sncf.fr...
> Hello,
>
> I'm trying to insert into a table consecutive pairs from another table.
>
> Right now, I'm using a cursor but I wonder if there's a faster way.
>
> Example:
>
> My table1 contains a column of integers: 1, 20, 3, 45, 12
>
> I want to obtain this in a two columns table Table2 :
> (1;3) (3;12) (12;20) (20;45)
>
> As I said, I use a cursor. I first sort Table1, and then go through it,
> inserting every line with the one of previous fetch.
>
> Is this possible with a double select on Table1, the second one
> skipping the first line ?

> select t1.col as col1, min(t2.col) as col2
> from table1 t1 inner join table1 t2 on t2.col > t1.col
> group by t1.col
> order by t1.col
>
>
> Works fine provided there are no duplicate values in table1. It is also
> surprisingly quick...

"Branko Radovanovic" <branko.radovanovic@ve-mil.REMOVE.hr> wrote in message
news:4c88k1-vc12.ln1@news.ve-mil.hr...
> > select t1.col as col1, min(t2.col) as col2
> > from table1 t1 inner join table1 t2 on t2.col > t1.col
> > group by t1.col
> > order by t1.col
> >
> >
> > Works fine provided there are no duplicate values in table1. It is also
> > surprisingly quick...
>
> ...as long as your row count is reasonably low, I must add...

> "Branko Radovanovic" <branko.radovanovic@ve-mil.REMOVE.hr> wrote in
message
> news:4c88k1-vc12.ln1@news.ve-mil.hr...
> > > select t1.col as col1, min(t2.col) as col2
> > > from table1 t1 inner join table1 t2 on t2.col > t1.col
> > > group by t1.col
> > > order by t1.col
> > >
> > >
> > > Works fine provided there are no duplicate values in table1. It is
also
> > > surprisingly quick...
> >
> > ...as long as your row count is reasonably low, I must add...
>
> This is in fact *much* faster:
>
> create table #temp (id int identity, col int)
>
> insert into #temp
> select col
> from table1
> order by col
>
> select t1.col, t2.col
> from #temp t1 inner join #temp t2 on t2.id = t1.id + 1
> order by t1.col
>
>
> The identity column trick...
>
> --
> Branko Radovanovic

"myname" <myname@ihatespam.com> wrote in message
news:c4ti3p$3ok$1@muguet.sncf.fr...

> Thank you very much for your help.
>
> But... isn't it the method I suggested in my last post of yesterday ?
>
> Except that I included the subset thing...