Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 16 of 16 total |
HAVING clause without GROUP BY |
Thu, Apr 20 2017 7:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
If you have time and an example available I'd be fascinated to see just what sort of thing you want this for. Whilst I'm sure when I see one I'll go "well that's obvious" I'm having trouble trouble visualising what it might do. Roy Lambert |
Thu, Apr 20 2017 4:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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. >> This was done intentionally after a lot of back-and-forth with existing DBISAM customers. I think what I can do here is add some session options for SQL compatibility, something along the lines of "hey, warn me when I do *this*"... Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 20 2017 7:07 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Roy,
I find it useful is doing statistics - such as dividing a table into quartiles. This example is copied from Joe Celko's SQL Puzzles. SalesData Table * District * Salesperson * SalesID * Amount The query below gives the top 3 sales in each district. SELECT * FROM SalesData AS S0 WHERE amount IN (SELECT amount FROM SalesData AS S1 WHERE S0.district = S1.district AND S0.amount <= S1.amount HAVING COUNT(*) <= 3) Richard |
Thu, Apr 20 2017 7:35 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | <<This was done intentionally after a lot of back-and-forth with existing DBISAM customers. I think what I can do here is add some session options for SQL compatibility, something along the lines of "hey, warn me when I do *this*"...>>
Great idea. I did not realise that it was useful. Richard |
Fri, Apr 21 2017 3:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
I'm feeling extremely thick here. I can follow the statement until it reaches the HAVING then my brain gives up. Is it taking the result of the WHERE clause up to that point and then further limiting it to just those where 3 or less rows would be returned or what? Roy Lambert ps I'm not interested enough to go and spend money on the book especially when I saw the price on Amazon |
Fri, Apr 21 2017 5:20 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Roy,
Does this help? "District" | "SalesPerson" | "SalesID" | "Amount" 1 | "Curly" | 5 | 3 1 | "Harpo" | 11 | 4 1 | "Larry" | 1 | 50 1 | "Fred" | 2 | 60 SELECT S0.SalesPerson, S0.Amount, S1.SalesPerson, S1.Amount FROM SalesData AS S0 INNER JOIN SalesData AS S1 ON S0.district = S1.district AND S0.amount <= S1.amount ORDER BY S0.Amount, S1.Amount "SalesPerson" | "Amount" | "SalesPerson1" | "Amount1" "Curly" | 3 | "Curly" | 3 "Curly" | 3 | "Harpo" | 4 "Curly" | 3 | "Larry" | 50 "Curly" | 3 | "Fred" | 60 "Harpo" | 4 | "Harpo" | 4 "Harpo" | 4 | "Larry" | 50 "Harpo" | 4 | "Fred" | 60 "Larry" | 50 | "Larry" | 50 "Larry" | 50 | "Fred" | 60 "Fred" | 60 | "Fred" | 60 If you group the above on SalesPerson, then the top 3 have a count of 3 or less. Richard |
« Previous Page | Page 2 of 2 | |
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 |