Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Subquery issues/help
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

17 Posts

Posted - 03/19/2013 :  09:54:52  Show Profile  Reply with Quote
This seems like something that would be commonly asked, but I can't seem to find my own answer, so sorry if it's a FAQ.

We have a new asset management system that automatically collects information about computers on our network. We have to build a view that is used by our help desk system to import asset information. One of the nice features is that the asset management system goes out and retrieves warranty information from the manufacturer. The bad news is that it doesn't distinguish between types of warranties if there are multiple types (such as full warranty, parts only, etc.). For our view, we'd like to retrieve just the date that is furthest out.

Simplified Data Structure:


Example Data:
TableAssets (AssetID, AssetName, AssetInfo)
1,Computer1,Notebook Computer
2,Computer2,Desktop Computer

TableWarranties (WarrantyID, AssetID, WarrantyExpireDate)

I want to return a single row for each asset with the "biggest" warranty date like:
1,Computer1,Notebook Computer,1/15/2017
2,Computer2,Desktop Computer,3/16/2015

Any suggestions on a query structure to generate this? If I can get past the basic setup I'm sure I can customize it to the larger dataset that we're working with.

Thanks in advance.

Flowing Fount of Yak Knowledge

2242 Posts

Posted - 03/19/2013 :  10:00:47  Show Profile  Reply with Quote
SELECT DISTINCT a.*, MAX(WarrantyExpireDate) OVER(PARTITION BY AssetID ORDER BY WarrantyExpireDate DESC) WarrantyDate
FROM TableAssets a
JOIN TableWarranties b
ON a.AssetID = b.AssetID

Go to Top of Page

Flowing Fount of Yak Knowledge

8781 Posts

Posted - 03/19/2013 :  10:07:05  Show Profile  Visit webfred's Homepage  Reply with Quote
if you don't need the WarrantyID then you can do this:

select t1.AssetID, t1.AssetName, t1.AssetInfo, t2.WarrantyExpireDate
from TableAssets as t1
left join
(select AssetID, max(WarrantyExpireDate) as WarrantyExpireDate from TableWarranties group by AssetID) as t2
on t1.AssetID = t2.AssetID

Too old to Rock'n'Roll too young to die.
Go to Top of Page

Starting Member

17 Posts

Posted - 03/19/2013 :  10:58:44  Show Profile  Reply with Quote
Thanks webfred, that got me on the right track and I was able to convert that to the real world and get it working.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000