Hi,



I responded to this ages ago already, but it seems that email never arrived
.. strange.



Well anyways, what I wanted to do was to implement a MEDIAN function using
FIRST n SKIP m. I ultimately solved the problem with a sub-select instead:



SELECT AVG(TOT_Dur) FROM

(SELECT FIRST x SKIP y TOT_Dur FROM HTTP_Events ORDER BY TOT_Dur)



I also agree there should be some kind of error message when now there is
none, but as this is not a showstopper error/bug/feature I think we can all
live with it for now and concentrate on more serious errors.



Thanx for all the answers, I did get some good explanations on why my
initial queries didn't work (especially to Helen)!



Regards,

Kim





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Fabricio Araujo
Sent: 3. lokakuuta 2009 06:23
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Strange FIRST n SKIP m + Function behaviour
in FB 2.5 Beta 2





Kim Sandell (Celarius) escreveu:

>
>
> Hi,
>
> I've got a few SQL Queries that behave strangely with FB v2.5 Beta 2:
>
> 1st the OK situations:
>
> SELECT FIRST 3 Dur_Total FROM HTTP_Events
> gives 3 result rows: 0.5575, 1.2298, 0.7567
>
> SELECT FIRST 2 SKIP 1 Dur_Total FROM HTTP_Events
> gives 2 result rows: 1.2298, 0.7567

First things first, you need a ORDER BY before you can say
that. Otherwise, your result are just incidental, as any
update on those records(even not on Dur_Total) can make
these results change, so they are NOT reliable AT ALL.

That said...

>
> Now the problem statements:
>
> SELECT FIRST 2 SKIP 1 Min(Dur_Total) FROM HTTP_Events
> gives 0 result rows????? Correct answer would be 1 row with value 0.7567
> The same happens with MIN(), MAX(), AVG(), SUM() etc...
> Event COUNT() gives an empty result...

As I understand, the First..Skip thing works this way :
1 - Execute the query as without FIRST..SKIP
2 - Take the resultset and skip the desired rows
3 - Read the FIRST n rows

Under that premise, that part is correct. Since you skipped
the only record existant, what you expected than empty resultset?

> And other non-working statements:
>
> SELECT FIRST 1 SKIP 0 Min(Dur_Total) FROM HTTP_Events
> gives 1 result row: 0.0000 - Which is wrong, should be 0.5575
>
> SELECT FIRST 1 SKIP 0 Avg(Dur_Total) FROM HTTP_Events
> gives 1 result row: 9.7764 - Wrong again ...
>
> SELECT FIRST 1 SKIP 0 Max(Dur_Total) FROM HTTP_Events
> gives 1 result row: 62.1193 - This is even stranger....

Those strange results are kinda bizarre, I agree...
Hmmmm... I though that kind of query would gain an "invalid syntax"
or "FIRST..SKIP cannot operate on a singleton aggregate without an GROUP
BY and/or ORDER BY clause" message...
Those queries deserve it......

>
> FB version: WI-T6.3.0.24643 Firebird 2.5 Beta 2
>
> The HTTP_Events table DUR_Total field is a FLOAT.
>
> Has anyone encountered this before? Or am I using the MIN,MAX,AVG etc.
> functions wrong somehow?

[Non-text portions of this message have been removed]