|
|
Posted by Mike C# on 06/15/06 03:34
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]
|