Den 2011-09-20 08:43 skrev Helen Borrie såhär:

> At 05:53 PM 20/09/2011, Kjell Rilbe wrote:
> >Consider this SQL:
> >
> >select
> > case when "RemovedAt" is null then 'Cur' else 'Hist' end "Category",
> > count(*) "Count"
> >from "Objects"
> >where "ObjectKind" = 92
> >group by case when "RemovedAt" is null then 'Cur' else 'Hist' end
> >order by case when "RemovedAt" is null then 'Cur' else 'Hist' end
> >
> >Why would that SQL give me this error:
> >
> >SQL Message : -104
> >Invalid token
> >
> >Engine Code : 335544569
> >Engine Message :
> >Dynamic SQL Error
> >SQL error code = -104
> >Invalid expression in the select list (not contained in either an
> >aggregate function or the GROUP BY clause)
> >
> >I've copied and pasted the group by clause into both order by and
> >select, so I think it should be alright.
>
> It's wrong because grouping must be on an output field. You seem to have
> gotten yourself a little mixed up here: the field you want to group and
> order on is "Category" - not the values in it!

I was under the impression that the query parser would recognize that
the two expressions are identical. That's actually how it works in SQL
Server 2008. In fact, there you HAVE to use that syntax; you CANNOT
refer to the select list's column alias in the group by clause. I wasn't
aware that SQL Server and Firebird are different in this respect.

> Just change it to
> group by 1
> order by 1

It's new to me that you can use the column index syntax for group by.
Interesting. It does NOT work in SQL Server 2008...

> You will get the groups in order anyway, so you don't actually need the
> ORDER BY clause.

But this is just an implementation artefact, isn't it? Not something
that's actually defined in the language?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64