469,579 Members | 1,181 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Query Help Please

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.

I have tried both Distinct and Group By and can get it close but not
quite where I need it.
Thanks

Jeff

Jul 23 '05 #1
2 989
[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
Jul 23 '05 #2

That works great!

Thank you very much.
*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Max Harvey | last post: by
6 posts views Thread by Nicolae Fieraru | last post: by
5 posts views Thread by Ryan Hubbard | last post: by
4 posts views Thread by Doris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.