Urgent: How to select the Maximum value 1

[XeroLui]

In my current situation, there are 2 columns of data that one is staff no., another is effective date...each staff no. will have more than 2 effective dates...now
i want to select the latest effective date of each staff no.
that for example...
staff no. effectivedate
00001 01/06/2001
00001 16/06/2001
00002 02/06/2001
00002 05/06/2001
00002 14/06/2001

in such situation, what crystal syntex can i type to select the report data in
00001 16/06/2001 and 00002 14/06/2001
Pls..urgent......

 

[rhinok]

There are many ways that you could accomplish this goal, depending on what your other requirements are. Presuming that this is the only data you want returned, you could easily accomplish your goal by:

1) Adding the Staff No.and Effective Date fields to your detail section.
2) Grouping by Staff No.
3) Right-clicking on Effective Date and selecting "Insert Summary"
4) Select the type of Summarization as "Maximum" for the Group "Staff No."
5) Move the newly created summary field to the Group Header.
6) Suppress the Detail Section.

At this point your report will be gouped by Staff No. and will display the latest (Maximum) Effective Date within the same section.

Hope this helps and good luck

 

[IdoMillet]

If performance is an issue, a faster alterantaive
is to use an SQL query:

Select staff_no, Max(effectivedate)
FROM...
GROUP BY staff_no

Cheers,
- Ido
ixm7@psu.edu