Find maximum pk2_id when pk1_id = fk_pk1_id Easy?? 1

[ehetzel]

I have 10 records where table1.pk1_id = table2.fk_pk1_id

I only want to select 1 record out of the 10
The 1 record with highest value for table2.pk_id2


table1 table2
pk1_id pk2_id
fk_pk1_id

I left outer join the tables on table1.pk1_id=table2.fk_pk1_id

This is what i've tried and getting error on maximum funct.

If {table1.pk1_id} = {table2.fk_pk1_id} then
Maximum({table2.pk_id2});

 

[fisheromacse]

it sounds like you need to create a SQL Expression.

try something like this for the SQL Expression:
(
Select max("pk_id2")
from table2
where "table2"."pk1_id" = "table1"."pk1_id"
)

 

[ehetzel]

How does crystal apply the sql expression to data?
before or after selection formual?

 

[lbass]

Assuming you have linked the two tables in the linking expert, you could also just use group selection (report->selection formula->GROUP->then enter):

table2.pk_id2 = maximum(table2.pk_id2, table1.pk1_id)

...assuming you have inserted a group on table1.pk1_id.

You could use the SQL expression approach, as long as you build in any selection criteria into the SQL expression, as it accesses the database directly, without honoring the selection formula.

-LB

 

[fisheromacse]

i am unclear on what you are wanting to know.

i think the SQL Expression is calculated while reading records, not after.

You can use the SQL Expression in the Select Expert.
ie: table1.pk1_id1 = {%MaxTable2pk1id}