Duplicate problems - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicate problems

Author

Topic

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-04-07 : 20:44:04

I have a table where I keep track of software version and machines that they are installed on, the problem is that 1 machine can have 2 version of a product, I only want to return the highest version, with the display name of the product.

Can someone help me?

Here is an example of the data I have.

Machine | Display Name | Version
---------|---------------------------------|--------
FA309050 | Microsoft Office XP | 10.2.4
FA309050 | Microsoft Office XP w/FrontPage | 10.2.4
FA330874 | Microsoft Office XP | 10.2.4
FA330874 | Microsoft Office 2003 | 11.1.00

What this should return is

FA309050 | Microsoft Office XP w/FrontPage | 10.2.4
FA330874 | Microsoft Office 2003 | 11.1.00

Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-07 : 22:21:05

[code]select Machine, [Display Name], max(Version)
from yourtable
group by Machine, [Display Name][/code]

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-04-07 : 22:43:57

tried that it doesnt work, still returns multiple records per machine

TimS
Posting Yak Master

198 Posts

Posted - 2006-04-07 : 23:21:10

select Machine, "Product", max(Version)
FROM
(
SELECT 'FA309050', 'Microsoft Office XP', '10.2.4' UNION ALL
SELECT 'FA309050', 'Microsoft Office XP w/FrontPage', '10.2.4' UNION ALL
SELECT 'FA330874', 'Microsoft Office XP', '10.2.4' UNION ALL
SELECT 'FA330874', 'Microsoft Office 2003', '11.1.00'
) AS X ("Machine", "Display Name", Version)
JOIN
(
SELECT 'Microsoft Office', 'Microsoft Office XP' UNION ALL
SELECT 'Microsoft Office', 'Microsoft Office XP w/FrontPage' UNION ALL
SELECT 'Microsoft Office', 'Microsoft Office XP' UNION ALL
SELECT 'Microsoft Office', 'Microsoft Office 2003'
) AS Y ("Product", "Display Name") ON X."Display Name" = Y."Display Name"
group by Machine, "Product"

Y Should be a Table in Your Database

Tim S

HalaszJ
Yak Posting Veteran

59 Posts

Posted - 2006-04-07 : 23:31:14

my table has a few hundred thousand rows, i think TimS's method is unfeasable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-08 : 00:08:06

try this

    select Machine, 
           max([Display Name]) as [Display Name]
           max(Version) as MaxVersion
    from   yourtable
    group by Machine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources