Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 16 total |
HAVING clause without GROUP BY |
Tue, Apr 18 2017 11:44 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Wed, Apr 19 2017 4:12 AM | Permanent Link |
Matthew Jones | 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 |
Wed, Apr 19 2017 5:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Apr 19 2017 1:31 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Apr 19 2017 3:42 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Wed, Apr 19 2017 3:43 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<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 |
Wed, Apr 19 2017 4:00 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Thu, Apr 20 2017 12:36 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Thu, Apr 20 2017 12:40 AM | Permanent Link |
Terry Swiers | > 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. |
Thu, Apr 20 2017 4:21 AM | Permanent Link |
Matthew Jones | Thanks for the detailed response.
-- Matthew Jones |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |