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.

distinct and order by

3 posts in General Discussion Last posting was on 2003-07-11 11:36:48.0Z
MicheleCS Posted on 2003-07-11 08:33:05.0Z
From: "MicheleCS" <michele.mazzucco@csteam.com>
Subject: distinct and order by
Date: Fri, 11 Jul 2003 10:33:05 +0200
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <u8Zd9A5RDHA.344@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: ppp-217-133-10-143.cust-adsl.tiscali.it 217.133.10.143
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1315
Article PK: 3556

I am using an ASA8 DB (8.0.2 build 4089)

I am trying to do the following:

select distinct(column_1), column_2
from table
order by column_3;

This worked fine with ASA7, but gives an error on ASA8 (same DB file, just
unloaded from ASA7 and upgraded to ASA8)

Seems like this syntax is invalid for ASA8...
Is it true? or what can i do to make it work?

Thanks


Robert Waywell Posted on 2003-07-11 11:03:23.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
References: <u8Zd9A5RDHA.344@forums-2-dub>
Subject: Re: distinct and order by
Date: Fri, 11 Jul 2003 07:03:23 -0400
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <#DFjz05RDHA.238@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: rwaywell-pc.sybase.com 172.31.143.74
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1317
Article PK: 3551

I just tried this on 7.0.3.2079 and 8.0.2.4218. In both cases the query with
the DISTINCT generated an error. I tested this using asademo.db.

select emp_lname, emp_fname from employee order by street
- this worked in both versions

select distinct emp_lname, emp_fname from employee order by street
- this failed in both versions, in 7.x it generated SQLCode -149, in 8.x it
generated SQLCode -854
- bracketing the first column in the select list did not change this
behaviour

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

"MicheleCS" <michele.mazzucco@csteam.com> wrote in message
news:u8Zd9A5RDHA.344@forums-2-dub...
> I am using an ASA8 DB (8.0.2 build 4089)
>
> I am trying to do the following:
>
> select distinct(column_1), column_2
> from table
> order by column_3;
>
> This worked fine with ASA7, but gives an error on ASA8 (same DB file, just
> unloaded from ASA7 and upgraded to ASA8)
>
> Seems like this syntax is invalid for ASA8...
> Is it true? or what can i do to make it work?
>
> Thanks
>
>
>
>


Breck Carter [TeamSybase] Posted on 2003-07-11 11:36:48.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Subject: Re: distinct and order by
Date: Fri, 11 Jul 2003 07:36:48 -0400
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <3s7tgv8v7vn3spdaojqr044990imlp5o3o@4ax.com>
References: <u8Zd9A5RDHA.344@forums-2-dub>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: bcarter.sentex.ca 64.7.134.118
Lines: 43
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1318
Article PK: 3553

First of all, the (brackets) around (column_1) have nothing to do with
DISTINCT.

The DISTINCT keyword may be placed after the SELECT keyword, and it
applies to the entire result set... every row must have a distinct set
of values, in this case every returned row of column_1 and column_2
values will be distinct.

The (brackets) have no effect; you can put brackets around any
expression, and "column_1" is just a simple example of an expression.

You're not the first to be confused by this, if it makes you feel
better... I think folks confuse SELECT DISTINCT with COUNT ( DISTINCT
expression ).

Second, you cannot ORDER BY a column that isn't in the result set, if
you use DISTINCT. I believe there was a flaw in early versions of ASA7
where this wasn't detected; I had a client with *exactly* the same
problem, and Glenn Paulley had to point it out for me.

Anyway, think about SELECT DISTINCT X FROM T ORDER BY Y. If two rows
in T have the same value of X but different values of Y, how is the
single resulting row to be sorted? It is not solvable.

Breck

On Fri, 11 Jul 2003 10:33:05 +0200, "MicheleCS"

<michele.mazzucco@csteam.com> wrote:

>I am using an ASA8 DB (8.0.2 build 4089)
>
>I am trying to do the following:
>
>select distinct(column_1), column_2
>from table
>order by column_3;
>
>This worked fine with ASA7, but gives an error on ASA8 (same DB file, just
>unloaded from ASA7 and upgraded to ASA8)
>
>Seems like this syntax is invalid for ASA8...
>Is it true? or what can i do to make it work?
>
>Thanks
>
>
>

bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
http://www.risingroad.com