Query Help

On Sat, 29 Mar 2003, shreedhar wrote:

Using this query I am expecting result as

year month totalreported closederrors openerrors fixederrors
2002 1 31 29 2

2002 2 85 60 25

2002 3 16 12 4
2002 5 41 29 12
2002 7 48 48
2002 8 1 1
2002 10 51 51
2002 11 69 15 54
2002 12 8 8

2003 1 9 9

2003 2 38 38

But Retreiving result as

year month totalreported closederrors openerrors fixederrors
2002 1 31 29 2
2002 1 31 29 9
2002 2 85 60 25
2002 2 85 60 38
2002 3 16 12 4
2002 5 41 29 12
2002 7 48 48
2002 8 1 1
2002 10 51 51
2002 11 69 15 54
2002 12 8 8
2003 1 9 29 2
2003 1 9 29 9
2003 2 38 60 25
2003 2 38 60 38

I mean I values retreives across years data is repeating.

May I know how can I get expected results by above query.

Hmm, what version are you using and what are the definitions of the tables
and what do the component subselects generate? I don't see anything
obviously wrong (although I'd think that the distinct clauses are
unnecessary with the group by), so I could have missed something or you
could be hitting a bug, but without something to plug in to try with it's
hard to tell.

Hi List,

I know it isn't possible to do queries like:
SELECT 1 AS c, c + 1 AS d;

Other queries that aren't possible are those:
SELECT intfield AS a, intfield * intfield AS square FROM tableX WHERE a
< 10 AND square < 50

Of course, these are bogus examples to simply illustrate my point :)
But is there a good reason not to support it or is it something like
"not yet implemented", "not interesting" or "to complex to (easily)
implement".

Best regards,

Arjen

On Sat, 29 Mar 2003, Arjen van der Meijden wrote:

I know it isn't possible to do queries like:
SELECT 1 AS c, c + 1 AS d;

Other queries that aren't possible are those:
SELECT intfield AS a, intfield * intfield AS square FROM tableX WHERE a
< 10 AND square < 50

Of course, these are bogus examples to simply illustrate my point :)
But is there a good reason not to support it or is it something like
"not yet implemented", "not interesting" or "to complex to (easily)
implement".

For the second, in the theoretical model, where clause entries are
processed before the select list is evaluated. You don't want to evaluate
the entire select list before testing the where (what if there's an
expensive subselect or function). You could get around that by only doing
the referenced ones, but you still run into issues if a select list entry
has the same name as a field in one of the from entries (since to be
complient it must be the field not the select list entry afaics). There'd
have to be a good definition and some real gain (for particularly
complicated cases you can use subselect in from to avoid double typing of
the expression so I personally don't think that's good enough alone).

Arjen van der Meijden <acm@tweakers.net> writes:

I know it isn't possible to do queries like:
SELECT 1 AS c, c + 1 AS d;

But is there a good reason not to support it or is it something like
"not yet implemented", "not interesting" or "to complex to (easily)
implement".

It's not supported because it would violate the SQL spec. The spec is
perfectly clear about the scope of names, and a SELECT's output column
names aren't in scope anywhere in the SELECT itself (except in ORDER
BY). If we treated them as if they were, we'd break queries that rely
on the spec-mandated scoping --- think about cases where the output
column names happen to conflict with column names available from the
input tables.

You can however use a sub-select:
SELECT * FROM
(SELECT intfield AS a, intfield * intfield AS square FROM tableX) AS ss
WHERE a < 10 AND square < 50

Note that it'd be unwise to assume this technique will eliminate
double evaluations of expressions. But it saves having to type them
more than once, at least.

regards, tom lane

Afaik they don't work in MySQL.
Perhaps in the brand new 4.x series, but at least not in the 3.23
versions.

Arjen