Sybase ASE 15.5 select 1 in group by

[Former Member]

Hi,

I've writen a query like

select 1

from table x

join table y on x.id = y.id

group by y.col1, x.col2, x.col3

now I would expect Sybase to return 1 row containing 1 field containing the value 1 for each group. Insteed only one row is returned containing 1 field with the value 1.

Is this expected in Sybase?

If I change the select to contain the actual grouped columns then I get 22000+ results...

select y.col1, x.col2, x.col3

from table x

join table y on x.id = y.id

group by y.col1, x.col2, x.col3

[former_member188958]

The ability to write queries with GROUP BY that do not include all the columns from the GROUP BY in the SELECT is a TSQL extension of the ANSI standard, as is a SELECT clause without any aggregates.  As the docs say, the results can sometimes be hard to understand.

Substitute an aggregate for the simple literal and I think you will get a result more like what you were expecting (which truthfully doesn't seem very useful to me).

select 

max(1)

from sysobjects x

join sysindexes y on x.id = y.id

group by y.indid, x.id, x.name

go

ANSI would have you write the query with every column from the GROUP BY in the SELECT

select 

y.indid, x.id, x.name, max(1)

from sysobjects x

join sysindexes y on x.id = y.id

group by y.indid, x.id, x.name

go