Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Rows inserted using ORDER BY

7 posts in General Discussion Last posting was on 2010-01-11 17:20:44.0Z
Eddie_A Posted on 2009-10-13 09:31:07.0Z
From: Eddie_A <renzo.aquino@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Rows inserted using ORDER BY
Date: Tue, 13 Oct 2009 02:31:07 -0700 (PDT)
Organization: http://groups.google.com
Lines: 26
Message-ID: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com>
NNTP-Posting-Host: 167.230.104.96
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1255426267 32746 127.0.0.1 (13 Oct 2009 09:31:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 13 Oct 2009 09:31:07 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: m38g2000yqd.googlegroups.com; posting-host=167.230.104.96; posting-account=tGPBogoAAADskRZsAjqsJ5xZWzZ8rxax
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!m38g2000yqd.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28459
Article PK: 77703

The rows were inserted on temp table with select order by clause. Most
of our reports were created using this method and assumes that when it
retrieve these rows will get the same order. I have a bad feeling that
we will be facing some problems in the future

Just an example:
select product,amount into #tmp from t1 order by product
insert #tmp select 'total amount',sum(amount) from #tmp

select product,amount from #tmp

-----------------------------------------------
I would approach it this way:

select product,amount,seq=identity(10) into #tmp from t1 order
by product
insert #tmp select 'total amount',sum(amount),coun(*) from #tmp

select product,amount from #tmp order by seq


Your thoughts:

EddieA

Thanks


Eddie_A Posted on 2009-10-13 09:35:33.0Z
From: Eddie_A <renzo.aquino@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: Rows inserted using ORDER BY
Date: Tue, 13 Oct 2009 02:35:33 -0700 (PDT)
Organization: http://groups.google.com
Lines: 30
Message-ID: <dcf33a81-4633-4eb0-b6ad-ed0c2a6dc4f0@s31g2000yqs.googlegroups.com>
References: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com>
NNTP-Posting-Host: 167.230.104.96
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1255426534 1188 127.0.0.1 (13 Oct 2009 09:35:34 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 13 Oct 2009 09:35:34 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: s31g2000yqs.googlegroups.com; posting-host=167.230.104.96; posting-account=tGPBogoAAADskRZsAjqsJ5xZWzZ8rxax
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!s31g2000yqs.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28460
Article PK: 77702


On Oct 13, 5:31 pm, Eddie_A <renzo.aqu...@gmail.com> wrote:
> The rows were inserted on temp table with select order by clause. Most
> of our reports were created using this method and assumes that when it
> retrieve these rows will get the same order. I have a bad feeling that
> we will be facing some problems in the future
>
> Just an example:
> select product,amount into #tmp from t1 order by product
> insert #tmp select 'total amount',sum(amount) from #tmp
>
> select product,amount from #tmp
>
> -----------------------------------------------
> I would approach it this way:
>
> select product,amount,seq=identity(10) into #tmp from t1 order
> by product
> insert #tmp select 'total amount',sum(amount),coun(*) from #tmp
>
> select product,amount from #tmp order by seq
>
> Your thoughts:
>
> EddieA
>
> Thanks

Correction:
insert #tmp select 'total amount',sum(amount),coun(*) + 1 from
#tmp


Leonid Gvirtz Posted on 2009-10-13 09:56:33.0Z
From: Leonid Gvirtz <lgvirtz@yahoo.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Rows inserted using ORDER BY
References: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com> <dcf33a81-4633-4eb0-b6ad-ed0c2a6dc4f0@s31g2000yqs.googlegroups.com>
In-Reply-To: <dcf33a81-4633-4eb0-b6ad-ed0c2a6dc4f0@s31g2000yqs.googlegroups.com>
Content-Type: multipart/alternative; boundary="------------010009070203070609040309"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad44ed1@forums-1-dub>
Date: 13 Oct 2009 02:56:33 -0700
X-Trace: forums-1-dub 1255427793 10.22.241.152 (13 Oct 2009 02:56:33 -0700)
X-Original-Trace: 13 Oct 2009 02:56:33 -0700, vip152.sybase.com
Lines: 155
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28461
Article PK: 77704

Eddie_A wrote:

On Oct 13, 5:31 pm, Eddie_A <renzo.aqu...@gmail.com> wrote:
The rows were inserted on temp table with select order by clause. Most of our reports were created using this method and assumes that when it retrieve these rows will get the same order. I have a bad feeling that we will be facing some problems in the future Just an example: select product,amount into #tmp from t1 order by product insert #tmp select 'total amount',sum(amount) from #tmp select product,amount from #tmp ----------------------------------------------- I would approach it this way: select product,amount,seq=identity(10) into #tmp from t1 order by product insert #tmp select 'total amount',sum(amount),coun(*) from #tmp select product,amount from #tmp order by seq Your thoughts: EddieA Thanks
Correction: insert #tmp select 'total amount',sum(amount),coun(*) + 1 from #tmp

Hi

I agree with you that one should not rely on the order of rows returned by the query unless ORDER BY clause is specified.
You can try the approach listed below. It doesn't use temporary tables or identity columns.

create table t1 (product varchar(30), amount int)
go
insert into t1 values ('product_1', 1)
insert into t1 values ('product_2', 2)
insert into t1 values ('product_3', 4)
go

select product, amount
from
(
    select 1 as forder, product, amount
    from t1
    union all
    select 2 as forder, 'total amount' product, sum(amount) amount
    from t1
) a
order by forder, product
go

Hope it helps
Leonid Gvirtz


species8472 Posted on 2009-10-15 11:58:03.0Z
From: Species8472 <species8472@ergens.op.het.net>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.1.4pre) Gecko/20090915 Thunderbird/3.0b4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Rows inserted using ORDER BY
References: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com>
In-Reply-To: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad70e4b@forums-1-dub>
Date: 15 Oct 2009 04:58:03 -0700
X-Trace: forums-1-dub 1255607883 10.22.241.152 (15 Oct 2009 04:58:03 -0700)
X-Original-Trace: 15 Oct 2009 04:58:03 -0700, vip152.sybase.com
Lines: 30
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28477
Article PK: 77719


On 13/10/09 11:31 , Eddie_A wrote:
> The rows were inserted on temp table with select order by clause. Most
> of our reports were created using this method and assumes that when it
> retrieve these rows will get the same order. I have a bad feeling that
> we will be facing some problems in the future
>
> Just an example:
> select product,amount into #tmp from t1 order by product
> insert #tmp select 'total amount',sum(amount) from #tmp
>
> select product,amount from #tmp
>
> -----------------------------------------------
> I would approach it this way:
>
> select product,amount,seq=identity(10) into #tmp from t1 order
> by product
> insert #tmp select 'total amount',sum(amount),coun(*) from #tmp
>
> select product,amount from #tmp order by seq
>
>
> Your thoughts:
>
> EddieA
>
> Thanks

Unless an order by is used in the retrieving query, it is never
guaranteed to come out in the order you might expect.


Sherlock, Kevin [TeamSybase] Posted on 2009-10-15 15:24:13.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com> <4ad70e4b@forums-1-dub>
Subject: Re: Rows inserted using ORDER BY
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad73e9d$1@forums-1-dub>
Date: 15 Oct 2009 08:24:13 -0700
X-Trace: forums-1-dub 1255620253 10.22.241.152 (15 Oct 2009 08:24:13 -0700)
X-Original-Trace: 15 Oct 2009 08:24:13 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28482
Article PK: 77723


"Species8472" <species8472@ergens.op.het.net> wrote in message
news:4ad70e4b@forums-1-dub...
> Unless an order by is used in the retrieving query, it is never guaranteed
> to come out in the order you might expect.

Agreed. This is especially true going forward in ASE 15 and as query
execution engine evolves. Order is never guaranteed by physical storage and
it has always been that way. The fact that this type of query worked in the
past is simply circumstantial.

> On 13/10/09 11:31 , Eddie_A wrote:
>> The rows were inserted on temp table with select order by clause. Most
>> of our reports were created using this method and assumes that when it
>> retrieve these rows will get the same order. I have a bad feeling that
>> we will be facing some problems in the future
>>
>> Just an example:
>> select product,amount into #tmp from t1 order by product
>> insert #tmp select 'total amount',sum(amount) from #tmp
>>
>> select product,amount from #tmp
>>
>> -----------------------------------------------
>> I would approach it this way:
>>
>> select product,amount,seq=identity(10) into #tmp from t1 order
>> by product
>> insert #tmp select 'total amount',sum(amount),coun(*) from #tmp
>>
>> select product,amount from #tmp order by seq
>>
>>
>> Your thoughts:
>>
>> EddieA
>>
>> Thanks
>


Gareth Davies Posted on 2010-01-11 12:16:00.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Rows inserted using ORDER BY
References: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com> <4ad70e4b@forums-1-dub> <4ad73e9d$1@forums-1-dub>
In-Reply-To: <4ad73e9d$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b4b1680@forums-1-dub>
Date: 11 Jan 2010 04:16:00 -0800
X-Trace: forums-1-dub 1263212160 10.22.241.152 (11 Jan 2010 04:16:00 -0800)
X-Original-Trace: 11 Jan 2010 04:16:00 -0800, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28865
Article PK: 78107

We're facing a similar problem to this. We don't apply an order by
clause because the temp table is populated using a recursive sproc that
builds a tree. The temp table has no clustered index. Historically,
ASE 125.4 and below have always returned the results in the order they
were added, but now ASE 15.0 is sporadically reversing this order (I
assumed based on some on-the-fly optimisation).

What are our options?

I can't think of a way to solve this in the sproc/SQL. I could add an
indentity column to the temp table and order the results by that on the
way out, or we could sort the result set recursively in our middle
layer, but are there any options to switch on/off the optimiser that
might help here?

Thanks

Gareth

Sherlock, Kevin [TeamSybase] wrote:
> "Species8472" <species8472@ergens.op.het.net> wrote in message
> news:4ad70e4b@forums-1-dub...
>> Unless an order by is used in the retrieving query, it is never guaranteed
>> to come out in the order you might expect.
>
> Agreed. This is especially true going forward in ASE 15 and as query
> execution engine evolves. Order is never guaranteed by physical storage and
> it has always been that way. The fact that this type of query worked in the
> past is simply circumstantial.
>
>> On 13/10/09 11:31 , Eddie_A wrote:
>>> The rows were inserted on temp table with select order by clause. Most
>>> of our reports were created using this method and assumes that when it
>>> retrieve these rows will get the same order. I have a bad feeling that
>>> we will be facing some problems in the future
>>>
>>> Just an example:
>>> select product,amount into #tmp from t1 order by product
>>> insert #tmp select 'total amount',sum(amount) from #tmp
>>>
>>> select product,amount from #tmp
>>>
>>> -----------------------------------------------
>>> I would approach it this way:
>>>
>>> select product,amount,seq=identity(10) into #tmp from t1 order
>>> by product
>>> insert #tmp select 'total amount',sum(amount),coun(*) from #tmp
>>>
>>> select product,amount from #tmp order by seq
>>>
>>>
>>> Your thoughts:
>>>
>>> EddieA
>>>
>>> Thanks
>
>


Gareth Davies Posted on 2010-01-11 17:20:44.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Rows inserted using ORDER BY
References: <1ceed359-634b-4912-ba1a-8f7408e51e14@m38g2000yqd.googlegroups.com> <4ad70e4b@forums-1-dub> <4ad73e9d$1@forums-1-dub> <4b4b1680@forums-1-dub>
In-Reply-To: <4b4b1680@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b4b5dec@forums-1-dub>
Date: 11 Jan 2010 09:20:44 -0800
X-Trace: forums-1-dub 1263230444 10.22.241.152 (11 Jan 2010 09:20:44 -0800)
X-Original-Trace: 11 Jan 2010 09:20:44 -0800, vip152.sybase.com
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28868
Article PK: 78108

Ignore this. I decided to implement the identity column solutions since
the row order would only be guaranteed by an order by clause.

Gareth Davies wrote:
> We're facing a similar problem to this. We don't apply an order by
> clause because the temp table is populated using a recursive sproc that
> builds a tree. The temp table has no clustered index. Historically,
> ASE 125.4 and below have always returned the results in the order they
> were added, but now ASE 15.0 is sporadically reversing this order (I
> assumed based on some on-the-fly optimisation).
>
> What are our options?
>
> I can't think of a way to solve this in the sproc/SQL. I could add an
> indentity column to the temp table and order the results by that on the
> way out, or we could sort the result set recursively in our middle
> layer, but are there any options to switch on/off the optimiser that
> might help here?
>
> Thanks
>
> Gareth
>
> Sherlock, Kevin [TeamSybase] wrote:
>> "Species8472" <species8472@ergens.op.het.net> wrote in message
>> news:4ad70e4b@forums-1-dub...
>>> Unless an order by is used in the retrieving query, it is never
>>> guaranteed to come out in the order you might expect.
>>
>> Agreed. This is especially true going forward in ASE 15 and as query
>> execution engine evolves. Order is never guaranteed by physical
>> storage and it has always been that way. The fact that this type of
>> query worked in the past is simply circumstantial.
>>
>>> On 13/10/09 11:31 , Eddie_A wrote:
>>>> The rows were inserted on temp table with select order by clause. Most
>>>> of our reports were created using this method and assumes that when it
>>>> retrieve these rows will get the same order. I have a bad feeling that
>>>> we will be facing some problems in the future
>>>>
>>>> Just an example:
>>>> select product,amount into #tmp from t1 order by product
>>>> insert #tmp select 'total amount',sum(amount) from #tmp
>>>>
>>>> select product,amount from #tmp
>>>>
>>>> -----------------------------------------------
>>>> I would approach it this way:
>>>>
>>>> select product,amount,seq=identity(10) into #tmp from t1 order
>>>> by product
>>>> insert #tmp select 'total amount',sum(amount),coun(*) from #tmp
>>>>
>>>> select product,amount from #tmp order by seq
>>>>
>>>>
>>>> Your thoughts:
>>>>
>>>> EddieA
>>>>
>>>> Thanks
>>
>>