SELECT ONLY MAXIMUM RECORD 1

[rnsean]

I'm working with a SQL database and want to create a report which will only pull one record (the most recent).

The filed dc_datetime is the field I wish to select only the Maximum of.
visit_id is foreign key to the table which contains the records of assessments (the table I am trying to pull from)
dc_datetime is the field I wish to select only the maximum record.

**I know how to do this by having a parameter display, but I want it to automatically pull not be prompted.

I already have created a report which will select the current date and another that will select the past7days but want one that will simply pull the last one entered based upon the date value.

Help??

 

[synapsevampire]

What version of Crystal?

This is easily done using SQL if you have CR 9.

For CR 8.5, try the following:

In the report expert, Group on the date field, create a Total using Maximum of the date field, add a Top N of 1.

Under File->Report Options select Perform Grouping on Server.

This will create the SQL and pass it to the database.

-k

 

[rnsean]

I have 8.5.

I was browsing other threads and discovered the suggestion about grouping under selection formula>group and it now works. Never knew I could do that. Thanks!

 

[synapsevampire]

Right, that basically does the same thing, though the above should create pass through SQL, whereas a group alone may not.

-k