Skip to Content
avatar image
Former Member

Sybase ASE 15.5 select 1 in group by

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 09, 2015 at 04:18 PM

    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

    Add comment
    10|10000 characters needed characters exceeded