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

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

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

<<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

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

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