|
|
Posted by Erland Sommarskog on 07/19/05 00:15
[posted and mailed, please reply in news]
JeffM (jeffmc@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|