How to fetch last record based on date in RS?

[srinu1253]

I have somany records based on date?
But I need only the last record out of it?
ex
Date           Amount
31-5-2010    100
31-5-2010    200


The result should be

31-5-2010    200

as Amount 200 is the latest record..

[blom0344]

Unless your date holds a time component as well, then your example would be a tie. Databases do not typically store data in  a specific order, you needs to impose a specific order in your query.

Can we assume that the higher amount value - always -  indicates the 'later' date for a given date?

[laks]

if you are calculating large amount as latest record you can use rank ordered by date and amount

[blom0344]

if you are calculating large amount as latest record you can use rank ordered by date and amount

In that case it simply a matter of fetching max(amount) for each date.
The problem lies in the definition of LAST DATE

[Alp]

Srinu1253,

You may have a primary key in that table. What is is? Could it be of any help to track the order of inserts?

- Alp

[mvjcognos]

try this
max(date for amount)
or
max(amount for date)
it automatically fetch data based on the maximum date in the database

[srinu1253]

There is no time component for date?
And you should not take max of date based on amount.

[Lynn]

You might look into the rank function, but you really would need something on the record that identifies the latest record. Maybe there is another field with audit date that includes a time stamp.

Assuming you can find a reliable field to determine the latest record, create a query item called DateRank that ranks ascending on that field. For example: rank (ModifiedDateTime asc)

Then create another query item that is the maximum of the rank query item called MaxRank for the report. For example: maximum([DateRank] for report)

Lastly, include a filter to bring back only the latest record. For example: [DateRank] = [MaxRank]

[Alp]

... one more time, what is the primary key on the table?
Often times people have surrogate PKEYs that holds an ID that is autoincrement or sequence based growing value, so it could be used to find the last record for the same date.