Den 2011-09-20 10:48 skrev Svein Erling Tysvær såhär:

> > 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...
>
> Hi Kjell,
> I assume from your answer to Helen that what you really want is SQL that
> works with both Firebird and SQL Server 2008.

Oh, that was a thought! :-) No, my ref to SQL Server was just a
comparison. I work a lot in both - in general legacy stuff in SQL Server
and new stuff in Firebird. In general I like Firebird's SQL better but I
find SQL Server's query optimizer to be better at avoiding really lousy
query plans. The most prominent example is that I have never seen SQL
Server create a plan with an unindexed subquery that is executed once
for each "master record". I think in those situations it uses some other
construct than simply executing the unindexed subquery repeatedly.
Something for the FB team to examine and learn from maybe?

> So, does SQL Server 2008
> support CTEs? I.e. could something like:
>
> with TMP ("Category") as
> (select case when "RemovedAt" is null then 'Cur' else 'Hist' end
> from "Objects"
> where "ObjectKind" = 92)
>
> select "Category", count(*) "Count"
> from TMP
> group by "Category"
> order by "Category"
>
> work in both engines?

I don't really know, and don't need to find out, but thank you anyway
for your efforts! :-)

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