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..
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?
if you are calculating large amount as latest record you can use rank ordered by date and amount
Quote from: laks on 17 Jul 2010 05:33:22 PM
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
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
try this
max(date for amount)
or
max(amount for date)
it automatically fetch data based on the maximum date in the database
There is no time component for date?
And you should not take max of date based on amount.
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]
... 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.