|
|
Posted by bevanward on 06/15/06 03:40
Hi Mike
Thanks for your comprehensive response. I had always assumed that this
insert was dependable (sequential and contiguous) ... I guess I need to
go back and re-write anywhere I have existing code that made this
assumption.
Thanks again, most appreciated.
Cheers
Bevan
Mike C# wrote:
> You can't rely on an IDENTITY column to be assigned in a particular order or
> to not have gaps in the sequence, btw. Try assigning a rank value manually
> instead:
>
> CREATE TABLE #tmp (A VARCHAR(50),
> L FLOAT,
> C INT NOT NULL PRIMARY KEY)
>
> CREATE TABLE #tmp1 (Aa VARCHAR(50),
> Ll FLOAT(50),
> PRIMARY KEY (Aa, Ll))
>
> INSERT INTO #tmp1 (Aa, Ll)
> SELECT 'ABC', 123.45
> UNION SELECT 'DEF', 456.12
> UNION SELECT 'XYZ', 999.99
> UNION SELECT 'RST', 023.43
> UNION SELECT 'GHI', 146.56
>
> INSERT INTO #tmp (A, L, C)
> SELECT t1.Aa, t1.Ll, COUNT(*) Rank
> FROM #tmp1 t1
> INNER JOIN #tmp1 t2
> ON t1.Aa >= t2.Aa
> AND t2.Ll >= t2.Ll
> GROUP BY t1.Aa, t1.Ll
> ORDER BY t1.Aa, t1.Ll
>
> SELECT C, A, L
> FROM #tmp
> ORDER BY C
>
> DROP TABLE #tmp1
> DROP TABLE #tmp
>
>
> <bevanward@gmail.com> wrote in message
> news:1150341168.351876.35870@i40g2000cwc.googlegroups.com...
> > Hi Mike
> >
> > Thanks for your comment - C is the field in the target table of the
> > insert that I was hoping would increment in the same sequence as the
> > sort of Aa, Ll
> >
> > Cheers
> > Bevan
> >
> > Mike C# wrote:
> >> Try ORDER BY C
> >>
> >>
> >> <bevanward@gmail.com> wrote in message
> >> news:1150338610.291820.67350@h76g2000cwa.googlegroups.com...
> >> > Hi All
> >> >
> >> > I am finding unexpected results when inserted into a newly created
> >> > table that has a field of datatype int identity (1,1).
> >> >
> >> > Basically the order I sort on when inserting into the table is not
> >> > reflected in the order of the values from the identity field.
> >> >
> >> > Have I been wrong in assuming that it should reflect the order from the
> >> > sort?
> >> >
> >> > The code is ...
> >> >
> >> > create table tmp (A varchar(50), L float, C int identity(1,1))
> >> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> >> >
> >> > and I don't understand why the values in tmp.C aren't in the order
> >> > suggested by the sort.
> >> >
> >> > Any comments most appreciated
> >> > Bevan
> >> >
> >
Navigation:
[Reply to this message]
|