Querying most recent dates

[Guest]

Hi,
Is there any way to create a query to return records, given a criteria, with
the latest date. (date not explicitly specified). It would be a TOP 1 query
but done for every globalID
eg. -- I would return the 2nd and last records only.

GlobalId EffectiveDate Amount
102161 01-Dec-05 3819
102161 28-Feb-06 3806
102161 10-Feb-06 3000
990000 2-Feb-06 5500
990000 10-Feb-06 3785

Is the nly way programmatically?
Thanks

 

[Guest]

Try something like

Select T1.GlobalId, T1.EffectiveDate, T1.Amount From TableName as T1 Where
T1.EffectiveDate In (Select Top 1 T2.EffectiveDate From TableName As T2 Where
T2.GlobalId = T1.GlobalId Order By EffectiveDate Desc)

 

[John Spencer]

Assumption:
Your table name does not contain spaces or other special characters.

SELECT T1.GlobalID, T1.EffectiveDate, T1.Amount
FROM [YourTable] as T1 INNER JOIN
(SELECT GlobalID, Max(EffectiveDate) as LastDate
FROM YourTable as Temp
GROUP BY GlobalID) as T2
ON T1.GlobalID = T2.GlobalID AND T1.EffectiveDate = T2.LastDate

You can do this with two queries. Query one saved as QLastDate is a totals
query that groups on GlobalID and gets the max of effective date. Using the
saved query method allows you to have field and table names that require the
brackets.

SELECT GlobalID, Max(EffectiveDate) as LastDate
FROM YourTable as Temp
GROUP BY GlobalID

Query2 uses the saved query as a table and your table. You join GlobalID to
GlobalID and EffectiveDate to the LastDate.

 

[Guest]

Thanks Guys,
I ended up using Offer's statement and it worked very well. I even impressed
myself and was able to add parameters to the where statement for the same
field.

WHERE (((T1.EffectiveHedgeDate) In (Select Top 1 T2.EffectiveHedgeDate From
TblTrans As T2 Where T2.GlobalId = T1.GlobalId Order By EffectiveHedgeDate
Desc) And (T1.EffectiveHedgeDate) Between
[Forms]![FrmReportNames]![txtParamFromDate] And
[Forms]![FrmReportNames]![txtParamToDate]));

I like the use of the max function in John's code and will definitely keep
that in mind if I run into any problems.
Thanks a bunch.