I noticed three things i dont really understand

1. Why can i not use aggregates in where clauses .. e.g.
 select guid_id, count(vari) from table where count(vari) > 1 group by vari;

and why can't i do stuff like this
2. select guid_id, count(vari) as cnt from table where cnt > 1 group by vari;
as cnt is already definied in the sql ...

however i can do stuff like this:

select * from (
select
 pc2.Protocol_GUID, count(pc2.ProjectCompany_GUID) as cnt
from
 protocolcompany pc2
group by
 Protocol_GUID+ProjectCompany_GUID) as tbl
 where tbl.cnt > 1

which does exactly the thing i like .. so its possible right ?! *g* i am using a aggregate in a where case.

3. Why is the group-by data unordered ?! if i use mysql or mssql my group-by data is ordered ..

f.e.

talbe "doh"
a,b
1,1
1,2
1,3
1,4
1,5

select a,b from doh group by a order by b (DESC);

well the result will be the same ...
a,b
1,5

but it shouldn't cause so i don't have the possibility to stuff like this ..

finding the first or last order: select * from orders group by custno order by orderdate (DESC);

but perhaps i'am wrong

well we are quite happy with elevatedb its a great product

thx

Volker

>1. Why can i not use aggregates in where clauses .. e.g.
> select guid_id, count(vari) from table where count(vari) > 1 group by vari;

I may be missing something but isn't this what HAVING is designed for?

>and why can't i do stuff like this
>2. select guid_id, count(vari) as cnt from table where cnt > 1 group by vari;
>as cnt is already definied in the sql ...

All you're really saying with that is that even if you use AS you still can't use aggregates in a WHERE clause

>however i can do stuff like this:
>
>select * from (
> select
> pc2.Protocol_GUID, count(pc2.ProjectCompany_GUID) as cnt
> from
> protocolcompany pc2
> group by
> Protocol_GUID+ProjectCompany_GUID) as tbl
> where tbl.cnt > 1
>
>which does exactly the thing i like .. so its possible right ?! *g* i am using a aggregate in a where case.

Not really. My take on that if I tried to describe it is you're generating a memory table and then querying that. At the point you query it cnt is simply a column in a table not an aggregate.


>3. Why is the group-by data unordered ?! if i use mysql or mssql my group-by data is ordered ..

Why should it be ordered? Grouping essentially throws away any order.

>but it shouldn't cause so i don't have the possibility to stuff like this ..
>
>finding the first or last order: select * from orders group by custno order by orderdate (DESC);

I'm not 100% sure what you're saying here or what you want to achieve. If you group orders by custno you've essentially lost the orderdate information so how you can expect to find the last or first order (I assume by order date) I don't know. The information presented for non-agregates or the GROUP BY columns is not random but from other posts I think its either the first or last item in physical order on the disk - something like that anyway. Its not something you can guarantee to be what you want.

Roy Lambert [Team Elevate]

Thx so far ..

"I think its either the first or last item in physical order on the disk"

thats the point you get some kind of random/uncontrolled data .. so these columns should be NULL'd right ? .. *g*
on the other side, there should be a way of selecting what data should be displayed ... as already said e.g.  ORDER BY .. and than you would get, for example, the latest orders

select * from orders group by custno order by orderdate desc;

whats the other way ?

select * from customers  c
left outer join orders o on o.custno = c.id
where o.id = (select id from orders where custno = c.id order by orderdate range 0 to 1)

.oO( i know thats an ugly example )

however well i was wondered about that behavior .. nothing you can't work around ..

however thx for the answers

Roy Lambert wrote:

Volker

>1. Why can i not use aggregates in where clauses .. e.g.
> select guid_id, count(vari) from table where count(vari) > 1 group by vari;

I may be missing something but isn't this what HAVING is designed for?

>and why can't i do stuff like this
>2. select guid_id, count(vari) as cnt from table where cnt > 1 group by vari;
>as cnt is already definied in the sql ...

All you're really saying with that is that even if you use AS you still can't use aggregates in a WHERE clause

>however i can do stuff like this:
>
>select * from (
> select
> pc2.Protocol_GUID, count(pc2.ProjectCompany_GUID) as cnt
> from
> protocolcompany pc2
> group by
> Protocol_GUID+ProjectCompany_GUID) as tbl
> where tbl.cnt > 1
>
>which does exactly the thing i like .. so its possible right ?! *g* i am using a aggregate in a where case.

Not really. My take on that if I tried to describe it is you're generating a memory table and then querying that. At the point you query it cnt is simply a column in a table not an aggregate.


>3. Why is the group-by data unordered ?! if i use mysql or mssql my group-by data is ordered ..

Why should it be ordered? Grouping essentially throws away any order.

>but it shouldn't cause so i don't have the possibility to stuff like this ..
>
>finding the first or last order: select * from orders group by custno order by orderdate (DESC);

I'm not 100% sure what you're saying here or what you want to achieve. If you group orders by custno you've essentially lost the orderdate information so how you can expect to find the last or first order (I assume by order date) I don't know. The information presented for non-agregates or the GROUP BY columns is not random but from other posts I think its either the first or last item in physical order on the disk - something like that anyway. Its not something you can guarantee to be what you want.

Roy Lambert [Team Elevate]

Volker

>"I think its either the first or last item in physical order on the disk"
>
>thats the point you get some kind of random/uncontrolled data .. so these columns should be NULL'd right ? .. *g*

Nope the correct answer is that its meaningless to ask for them so rather than have them return NULL just leave them out of the query. Personally I think you shouldn't even be allowed to put such meaningless columns into the query apart from the fact that you could be using it to create another table and then altering the value in there.

> on the other side, there should be a way of selecting what data should be displayed ... as already said e.g. ORDER BY .. and than you would get, for example, the latest orders

Or would you get the first order? And for every person who thinks it should be the first row that supplies the values there'll be one who thinks it should be the last row <vbg>

Roy Lambert [Team Elevate]

yeah our right .. *g* .. k ... thx for the answers

Roy Lambert wrote:

Volker

>"I think its either the first or last item in physical order on the disk"
>
>thats the point you get some kind of random/uncontrolled data .. so these columns should be NULL'd right ? .. *g*

Nope the correct answer is that its meaningless to ask for them so rather than have them return NULL just leave them out of the query. Personally I think you shouldn't even be allowed to put such meaningless columns into the query apart from the fact that you could be using it to create another table and then altering the value in there.

> on the other side, there should be a way of selecting what data should be displayed ... as already said e.g. ORDER BY .. and than you would get, for example, the latest orders

Or would you get the first order? And for every person who thinks it should be the first row that supplies the values there'll be one who thinks it should be the last row <vbg>

Roy Lambert [Team Elevate]

Roy,

<< Personally I think you shouldn't even be allowed to put such meaningless columns into the query >>

I fully agree with you.
It wasn't allowed in the early EDB 2 releases, but at some point Tim decided to relax the rules and make it more like DBISAM. Personally I can't see any advantage, as it adds complexity to the GROUP BY rules but no additional functionality, as EDB has other ways to accomplish the same results.
Also, it's not standard SQL, and that means the "relaxed" rules would have to be fully documented in the EDB manuals, otherwise how would you say it should be working like this or that way...

--
Fernando Dias
[Team Elevate]

Volker,

<< 1. Why can i not use aggregates in where clauses .. e.g. select guid_id,
count(vari) from table where count(vari) > 1 group by vari; >>

As Roy indicated, you use HAVING for that.

<< and why can't i do stuff like this
2. select guid_id, count(vari) as cnt from table where cnt > 1 group by
vari;  as cnt is already definied in the sql ...  >>

You can, but in the HAVING clause.

<< 3. Why is the group-by data unordered ?! if i use mysql or mssql my
group-by data is ordered .. >>

GROUP BY *is* ordered, by default.  It orders things according to the GROUP
BY columns unless you specify an ORDER BY, in which case the ORDER BY will
override the GROUP BY sorting.

<< well we are quite happy with elevatedb its a great product >>

Thanks.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com