greatest date

[bzsurf03]

I am dealing with a set of data that has an autonumber for the primary key. This number actually represents a date which is also in the same table. I want a report that only shows information about the latest date, which would be the greatest auto number. How would I go about doing this?

 

[kovas]

sort it descending by date? put a count of records and suppress anything greater than 1?

 

[lyanch]

It depends on your data.. if you only have a few items per whatever it is that you are looking for.. then add a group, sort descending by the id and only display your data in the header (ie only the first record in the group).

If you have a lot of records that you only want one of, add a subquery to your select statement manually to only return the highest date:

table.date = (select max(t.date) from table t where t.identifier = table.identifier)

where identifier is the (probably foreign key) field you would be grouping on, such as customerId etc.

Lisa

 

[Naith]

You can also achieve this by using your Group Selection Criteria. Access the Report menu/Edit Selection Menu/Group, and enter the equivalent of:

{YourDate} = Maximum({YourDate})

Naith