Tim,

Perhaps you could add this to your EDB list?

The SQL standard says you can use the HAVING clause without a GROUP BY clause - which EDB does not allow. If the GROUP BY clause is missing then the table is classed as one group.

I find it useful for statistics when calculating the median or percentiles.  I wish to do things such as:

HAVING COUNT(*) <= <some condition>

Hopefully it is as simple as it sounds.

Richard

Richard Harding wrote:

> Hopefully it is as simple as it sounds.

Forgive me for asking, but I like to learn when people bring up topics I know nothing of. How would this actually work? Is there a default "group" if you don't specify one? Can't you specify that yourself, by grouping by the record number or something?

Ah, I think I understand the "HAVING" clause - not known that before. This is sort of like a SELECT WHERE column <= value and a COUNT column added? There must be more to it than that.

Nope, I think I am still confused. Can you give a more solid example please? (For me, not Tim!)

--

Matthew Jones

Richard


I may be looking at this from the wrong viewpoint but I wonder if a subselect as a part of the WHERE clause would work for your requirements?

Roy Lambert

Richard,

<< Perhaps you could add this to your EDB list? >>

Sure, no problem.  I'll be also picking up some SQL enhancements as part of the EDB platform updates, and I can include it then.

Tim Young
Elevate Software
www.elevatesoft.com

Matthew

The conceptional model for the SELECT statement is the following steps. It is not the way the working table is created in practice.

** Create a result table using the FROM clause.

** Keep the results of for the rows that return TRUE - rows that return FALSE or UNKNOWN are removed.

** GROUP  BY returns returns a row for each for each column where the GROUP BY column list have the same value. NULLS are treated as one group. If there is no GROUP BY then the entire table is treated as one group.

**  HAVING clause then operates on the grouped table. Given that only the GROUP BY columns and their aggregate functions are available in the table, then these are the only valid columns to use in the expression. You can use expressions based on these columns.

** Retain the columns that are selected in the SELECT list. If DISTINCT is used then duplicate rows are deleted. Given that only the GROUP BY columns and their aggregate functions are now available in the table then only these columns can be used in the SELECT column list.

Hopefully that makes sense and answers you question.
Richard

<<Sure, no problem.  I'll be also picking up some SQL enhancements as part of the EDB platform updates, and I can include it then.>>

Great -  thank you.
Richard

Roy,

<<I may be looking at this from the wrong viewpoint but I wonder if a subselect as a part of the WHERE clause would work for your requirements?>>

I sometimes want to use the WHERE clause that has EXISTS subselect using a HAVING clause. Given that the WHERE clause operates on the initial working table and the HAVING clause works on the GROUP BY table then it does not always work.

I am not trying to solve a specific problem at the moment. It would make a couple problems easier.

Richard

Tim

<<Sure, no problem.  I'll be also picking up some SQL enhancements as part of the EDB platform updates, and I can include it then.>>

Another thing that could be added to the list.

Some people have managed to get themselves into a bit of a tangle by including columns in the SELECT list that are not in GROUP BY column list. EDB does not flag this as any error which has caused some confusion.

Richard

> Some people have managed to get themselves into a bit of a tangle by including columns in the SELECT list that are not in GROUP BY column list. EDB does not flag this as any error which has caused some confusion.

And just to chime in here, the ability to do this with EDB sometimes makes life much easier with EDB than other databases.  So I'm hoping this never changes in EDB.

Thanks for the detailed response.


--

Matthew Jones