Change in SQL commands!

In VFP7 this commend work:

SELECT MAX(nJN2_1) as MaxTime, cJN2_2 from jn2

In this command I can see Max of nJN2_1 and cJN2_2 that have Max Value.
In VFP8 and VF9 when I run this command see an error:

SQL: GROUP BY Clause is missing or invalid.

Why? and if this command is changed, how we can know what record have Max
Value!

My E-Mail:
HeartRefine@Yahoo.com

Stefan
Mon Sep 13 23:21:15 CDT 2004

See Set EngineBehaviour in help.


hth
-Stefan

"Saed Modarresi" <HeartRefine@Hotmail.com> schrieb im Newsbeitrag
news:Os3c7mgmEHA.2096@TK2MSFTNGP15.phx.gbl...
> In VFP7 this commend work:
>
> SELECT MAX(nJN2_1) as MaxTime, cJN2_2 from jn2
>
> In this command I can see Max of nJN2_1 and cJN2_2 that have Max Value.
> In VFP8 and VF9 when I run this command see an error:
>
> SQL: GROUP BY Clause is missing or invalid.
>
> Why? and if this command is changed, how we can know what record have Max Value!
>
> My E-Mail:
> HeartRefine@Yahoo.com
>

Gene
Tue Sep 14 11:07:18 CDT 2004

"Saed Modarresi" <HeartRefine@Hotmail.com> wrote:

>In VFP7 this commend work:
>
>SELECT MAX(nJN2_1) as MaxTime, cJN2_2 from jn2
>
>In this command I can see Max of nJN2_1 and cJN2_2 that have Max Value.
>In VFP8 and VF9 when I run this command see an error:
>
>SQL: GROUP BY Clause is missing or invalid.
>
>Why? and if this command is changed, how we can know what record have Max
>Value!

Because you are combining aggregates and non-aggregates and the
non-aggregates are not referenced in a group by. The is undefined
behaviour.

max() returns one row. A non-aggregate could return one (but
also any other whole number). Which row do you propose be returned?

VFP has gotten tighter with its interpretation of SQL.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Anders
Tue Sep 14 12:30:14 CDT 2004

Hi Saed,

SELECT MAX(njn2_1) AS maxtime FROM Jn1
(without a GROUP BY) returns a single row with the max value for the entire
table.
The VFP7 query "SELECT MAX(nJN2_1) as MaxTime, cJN2_2 from jn2" returns a
random value for cjn_2 which has no defined connection with the max value
for anothere column.
The max value for of njn_2 may be in row 3 of 200; The value for cjn_2 that
VFP will return will be the bottom value in the table.
This value may very well be different the next day if you've loaded a sorted
backup of the table.
Given this table
njn_2 cjn_2
3 'F'
4 'A'
1 'X'
Your query in VFP7 will return
njn_1 cjn_2
4 'X'
which is meaningless; 4 and X don't belong in the same row.

For this reason the Standard SQL rules do not allow this kind of SQL,
If you want the entire row where njn_2 equals the max value for njn_2
there's a different query

SELECT * FROM Jn1 WHERE njn_2 = ;
(SELECT MAX(njn_2) FROM Jn1)

-Anders

"Saed Modarresi" <HeartRefine@Hotmail.com> wrote in message
news:Os3c7mgmEHA.2096@TK2MSFTNGP15.phx.gbl...
> In VFP7 this commend work:
>
> SELECT MAX(nJN2_1) as MaxTime, cJN2_2 from jn2
>
> In this command I can see Max of nJN2_1 and cJN2_2 that have Max Value.
> In VFP8 and VF9 when I run this command see an error:
>
> SQL: GROUP BY Clause is missing or invalid.
>
> Why? and if this command is changed, how we can know what record have Max
> Value!
>
> My E-Mail:
> HeartRefine@Yahoo.com
>
>