maximum wins only

[aspnet98]

I have a recordset displaying the following that i am using in crystal:

ID Week Wins Difference name
1 1 1 2 jason
1 1 1 5 john
1 1 0 24 jay

I am grouping on the id and then the week. The report looks perfect for what i want to do, except that i only want to show the winner (in this case it is jason).

How can i do this?

Sometimes their may be a tie.

ID Week Wins Difference name
1 1 1 2 jason
1 1 1 2 john

and i need to show the two who tied.

is this possible?

cr 8.5
sql server 2000

 

[aspnet98]

PS the top n sort is not available....

 

[aspnet98]

my approach was this:
set two fomulas in the details section
@max maximum(wins,week)
@min minimum(dif,week)

in the details section, suppress formula:
{Wins} < {@max} and
{diff} > {@min}

but this fails.........is their a better approach?

 

[GJParker]

What denotes that jason is the winner in your example ?



Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Is "wins" a summary or a database field?

-LB

 

[aspnet98]

wins is determined by most wins lowest difference

lb: wins is a database field

 

[lbass]

You need to do a selection within a selection, so I think the following approach would work. You could create a SQL expression {%maxwins} to return the highest number of wins:

(Select max(A.`wins`) from Table A where
A.`ID` = Table.`ID` and
A.`Week` = Table.`Week`)

Then you would use a record selection formula like:

{table.wins} = {%maxwins}

This should return all records with the maximum number of wins. Then to select the one or more records with the minimum {table.diff}, go to report->edit selection formula->GROUP and enter:

{table.diff} = minimum({table.diff},{table.week})

I'm assuming that "week" is actually a database field called {table.week}. If it is a date that you are grouping on weekly, you would have to adjust the expression and formula.

-LB