Maximum help

[kcsilentbob]

i need help with a maximum sql expression that I saw posted on this board.

Basically I am intendeding to display only the maximum date for each record. i saw that a sql expression is probably the best way to go. My problem is that I am setting maximum on a date field on table A, in a group of policy number field on Table B.

I tried this expression

(SELECT MAX(A.ENDDATIME#)
FROM
vwGW20 A
WHERE
A."INXFLD02" = vwGW03."INXFLD02")

and am getting this error

column prefix vwgw03 does not match table name or alias name used in the query.

I am on Crystal XI.

Please help

 

[synapsevampire]

Using subqueries depends on the database being used, so remember to post the basics.

Try something like:

(SELECT MAX(A.ENDDATIME#)
FROM
vwGW20 A, outertable b
WHERE
A."INXFLD02" = b."INXFLD02")

At any rate, whatever the sql you'd use for your database for a subquery should work there.

-k

 

[kcsilentbob]

I am not sure what you mean by sub query?

I tried what you recommended but had to modify it to this to get it to work.

(SELECT MAX(A.ENDDATTIM#)
FROM
vwGW20 A, vwGW03 b
WHERE
b.INXFLD02 = b."INXFLD02")

When I ran this it returned no records.

The INXFLD02 field is not on the vwgw20 table, it is only available on the vwgw03.