[posted and mailed, please reply in news]
JeffM (je****@huddlestonco.com) writes:
I have the following query:
SELECT TOP 100 PERCENT Company_Code, Well_Code, Effective_Date,
Interest
FROM dbo.HUD_Expense_Interest_Group1
I need to have the query return one row for each distinct Company_Code,
Well_Code combination along with returning the values in Interest and
Effective_Date. There will be multiple Interest and Effective_Date
values for each distinct Well_Code, Company_Code combination; I only
want the values (Interest & Date) from the records represented by the
newest date(Max) in Effective_date.
If I understand this correctly, this query should cut it:
SELECT a.Company_Code, a.Well_code, a.Effective_Date, a.Interest
FROM dbo.HUD_Expense_Interest_Group1 a
JOIN (SELECT Company_Code, Well_code,
Effective_Date = MAX(Effective_Date)
FROM dbo.HUD_Expense_Interest_Group1
GROUP BY Company_Code, Well_code) b
ON a.Company_Code = b.Company_Code
AND a.Well_code = b.Well_Code
AND a.Effective_Date = b.Effective_Date
What you have here is a derived table, which is a kind of a temp table
within the query, except that it is not necessarily materialised. As
long as the result is not affected, SQL Server may rearrange the
computation order.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
