an Sql command with max

[michellq]

Dear VFP user,

Please can someone help me with the next SQL statement:

SELECT p.perfkcmp, p.perpk, c.cervalid, MAX(c.cerinvalid) AS expiredate ;
FROM person p, certificate c ;
WHERE c.cerfkper = p.perpk ;
GROUP BY p.perpk ;
INTO CURSOR Cr_Temp

The question now:

In cursor Cr_Temp are records with the expiredate what is the maximum of c.cerinvalid but the information of c.cervalid is not from the record where the maximum is found.

How do I change the sql command so that c.cervalid and MAX(c.cerinvalid) come from the same record.

Nice regards,

Michelle.

 

[TomasDill]

Hi!

If you need just 1 record, you may try:

SELECT TOP 1 p.perfkcmp, p.perpk, c.cervalid, c.cerinvalid AS expiredate ;
FROM person p, certificate c ;
WHERE c.cerfkper = p.perpk ;
ORDER BY c.cerinvalid descending ;
INTO CURSOR Cr_Temp

If you're sure c.cerinvalid is unique between all groups (unique for all different c.cerfkper), you can use also following query. Note that that query might be acceptable for you even when c.cerinvalid is not unique, its depended on where and how you will use query result.

SELECT p.perfkcmp, p.perpk, c.cervalid, c.cerinvalid AS expiredate ;
FROM person p, certificate c ;
WHERE c.cerfkper = p.perpk ;
AND c.cerinvalid in ( ;
SELECT MAX(cc.cerinvalid) AS cerinvalid ;
FROM person pp, certificate cc ;
WHERE cc.cerfkper = pp.perpk ;
GROUP BY pp.perpk) ;
INTO CURSOR Cr_Temp




Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softserve.lviv.ua

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[foxdev]

The problem, Michelle, is in your group by. You are selecting non-aggregate columns that are not included in your group by. Try this:

[tt]SELECT p.perfkcmp, p.perpk, c.cervalid, MAX(c.cerinvalid) AS expiredate ;
FROM person p, certificate c ;
WHERE c.cerfkper = p.perpk ;
GROUP BY p.perfkcmp, p.perpk, c.cervalid ;
INTO CURSOR Cr_Temp[/tt]

Robert Bradley

http://www.foxdev.com/

 

[dgrewe]

I think your problem is a syntax error with the max command. You need 2 parameters for the max() function to work not one. [red]max(field1,field2) as expiredate[/red]
The select statement is seeing the syntax error and cratering.

The following is from the FoxProHackers Guide Help file
MAX() , MIN()
These two functions take a [red]list of values and return either the largest or smallest value in the list.[/red] Any of the numeric types (Numeric, Float, Integer, Currency, Double) can be used, as well as character, memo, date and datetime expressions. The result of numeric comparisons is Numeric unless all the values passed in are Currency, in which case the result is Currency. If Dates and Datetimes are mixed, Datetime is the result. All Memo and Character comparisons return type Character. General, Screen and Object variables yield an "Operator/operand type mismatch" or "Data type mismatch," as you might expect.
Usage nReturnValue = MAX( nExpr1, nExpr2 [, nExpr3 ... ] )
nReturnValue = MIN( nExpr1, nExpr2 [, nExpr3 ... ] ) David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644

 

[foxdev]

Dave, that's true of the VFP MAX() function, but not true of the SQL SELECT MAX aggregate function.

From the VFP online help on Select - SQL:

MAX(Select_Item), which determines the largest value of Select_Item in a column.

Robert Bradley

http://www.foxdev.com/

 

[dgrewe]

OK, Guess my lack of knowledge of VFP is showing. Tks for the info. David W. Grewe
Dave@internationalbid.com
ICQ VFP ActiveList #46145644

 

[michellq]

Thanks a lot for your information.

Nice regards,

Michelle.