Getting only maximum values

[Cris]

Hello
I'm using CR 8 and Oracle 8.
The joins between my tables require getting only maximum values for a date field.
I've tryed a simple query like:

SELECT
NHOPG."GRUPO_ENT", NHOPG."PUESTOG"
FROM
"NORMA50I"."NHOPG" NHOPG
WHERE
NHOPG."ANOTACION" = ( SELECT MAX(T2."ANOTACION&quot FROM "NORMA50P"."NHOPG" T2 )

but it shows nothing, and the table isn't empty.

I've tryed too a record selection formula like:
{NHOPG.ANOTACION} = Maximum ({NHOPG.ANOTACION})

but I get an error because CR can't know what is the maximum value.


Anyone knows how to do it?

** ANOTACION is a date field.

Thank you ;-)

 

[kenhamady]

Put:
{NHOPG.ANOTACION} = Maximum ({NHOPG.ANOTACION})

into the Group selection formula.
It will retrieve all of the records, but will only print the ones that meet the test. Of course, any totals will include all records that meet record selection criteria.

Use running totals to get accurate totals of reports with a Group Selection formula. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[MalcolmW]

Is this the sort of thing you would like to do?
SELECT
NHOPG."GRUPO_ENT", NHOPG."PUESTOG", MAX(NHOPG."ANOTACION&quot
FROM
"NORMA50I"."NHOPG" NHOPG
GROUP BY
NHOPG."GRUPO_ENT", NHOPG."PUESTOG" Malcolm

 

[Cris]

No. The complete problem is:
I get a date value from a parameter (ej: ?date1), and for each GRUPO_ENT, PUESTOG, I want to get the row (or rows) that contains the maximum value of "ANOTACION" date AND
?date1 is between FEDESVIG and FEHASVIG (two date fields).
Because I have to get the complete row I can't use your SELECT because it only gets the names of the groups and the maximum value of ANOTACION.

Sorry, I know my english is horrible...

 

[MalcolmW]

Are you familiar with SQL, or do you know someone who is? If so, is this the sort of thing you would like to do?
SELECT
NHOPG."GRUPO_ENT", NHOPG."PUESTOG", MAX(NHOPG."ANOTACION&quot
FROM
"NORMA50I"."NHOPG" NHOPG
WHERE
?Date1 BETWEEN NHOPG."FEDESVIG" AND NHOPG."FEHASVIG"
GROUP BY
NHOPG."GRUPO_ENT", NHOPG."PUESTOG"

You are not clear on your definition of MAX(NHOPG."ANOTACION&quot. It could be a maximum for the table, a maximum for the records in the table as filtered by your WHERE clause, a maximum for the group defined by the group by clause, or some other maximum value.
Because of the language barrier, I suggest trying to define what you want in SQL. Getting that result in SCR will then be easier.
Malcolm

 

[Cris]

For each PUESTOG in each GRUPO_ENT, I want to get the values of all the fields of the row with the maximum value of "ANOTACION". Something like:

SELECT
NHOPG.*
FROM
"NORMA50I"."NHOPG" NHOPG
WHERE NHOPG = (SELECT MAX (NHOPG2.ANOTACION)
FROM "NORMA50I"."NHOPG" NHOPG2
WHERE ?Date1 BETWEEN NHOPG."FEDESVIG" AND NHOPG."FEHASVIG&quot...

 

[Guest_imported]

Cris--I had a similar problem where I only wanted to see the most recent date. I created a nested select statement in my SQL similar to what you have above (Feb 26)--so keep that! Where your problem differs than mine is your parameter having to be between 2 fields. I would validate your parameter through the select expert on the database field {Date1} (if you have one) that ?Date1 is the parameter for. That way you can default your report to show all max. rows if the parameter does not meet the validation.

An example is (I hope I have all of this right):

if(isdate({?Date1}) and
({?Date1} < {NHOPG.FEDESVIG} and
({?Date1} < {NHOPG.FEHASVIG})
then
{Date1} = ({?Date1}
else
true