Need Distinct to apply only two fields

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

General SQL Server Forums

New to SQL Server Programming

Need Distinct to apply only two fields

Author

Topic

SarahBrochu
Starting Member

13 Posts

Posted - 2010-08-27 : 17:07:15

Hi,

I am trying to get the Unique Rows for Name field and ApplicationName Field only.
If I used Distinct it applies to all the fields. I only want Name and ApplicationName to return unique fields.

here is my script:

SET ROWCOUNT 0

SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid]
FROM vComputer vC
Join dbo.Inv_AeX_OS_Add_Remove_Programs OS1 ON OS1.[_ResourceGuid]= Vc.[Guid]
WHERE OS1.[Install Path] NOT LIKE 'C:\WINDOWS%'
AND OS1.[Hidden]='False'
AND OS1.[Install Path] NOT LIKE '%Altiris%'
AND OS1.[Install Path] NOT LIKE '%ATI%'
AND OS1.[Install Path] NOT LIKE 'C:\WINDOWS%'
AND OS1.[Uninstall Path] NOT LIKE '%Uninstall%'
AND OS1.[Publisher] Not Like '%Altiris%'
AND OS1.[_id] Not Like '134%'
AND OS1.[Name] Not Like '%MSXML%'
AND OS1.[Name] Not Like '%Update%'
And OS1.[Name] NOT Like '%Update%'
And OS1.[Name] Not Like '%Security%'
And OS1.[Name] Not Like '%Hotfix%'
And OS1.[Name] Not Like '%Hot fix%'
And OS1.[Name] Not Like '%driver%'
And OS1.[Name] Not Like '%help%'
And OS1.[Name] <> 'HighMAT Extension to Microsoft Windows XP CD Writing Wizard'
AND OS1.[Name] <> 'infocentral'
AND OS1.[Name] Not Like '%spelling dictionaries%'
AND OS1.[Name] Not Like '%care pack%'
AND OS1.[Name] Not Like '%HP Color%'
AND OS1.[Name] Not Like '%HP Deskjet%'
AND OS1.[Name] Not Like '%HP Laserjet%'
AND OS1.[Name] Not Like '%HP Officejet%'
AND OS1.[Name] Not Like '%HP Scanjet%'
AND OS1.[Name] Not Like '%fonts%'
AND OS1.[Name] Not Like 'Components Installer'
AND OS1.[Name] Not Like '%Altiris%'
AND OS1.[Name] Not Like '%Conexant%'
AND OS1.[Name] Not Like '%ATI%'
AND OS1.[Name] Not Like 'Microsoft .NET%'
AND OS1.[Name] Not Like '%Internet Explorer%'
AND OS1.[Name] Not Like '%(KB%'
AND OS1.[Name] Not Like '%Compatibility Pack for the 2007 Office System%'
Order by Publisher, [Application Name], vC.[Name]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-27 : 17:52:56

What values for OS1.[Publisher], OS1.[Hidden], OS1.[Version] and vC.[Guid] do you want? Random? If so, you could use MIN or MAX on those columns and group by Name and ApplicationName. Alternatively, you could use a ranking function like ROW_NUMBER().

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-28 : 00:49:21

also see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SarahBrochu
Starting Member

13 Posts

Posted - 2010-08-31 : 13:55:53

Hello Lamprey,

Can you point me to some sample for the row_number please?

I want all fields diplayed but want unique only first two fields
vC.[Name], OS1.[Name]

SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid]

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 12:27:15

something like


select columns...
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,row_number() over (partition by vC.[Name], OS1.[Name] order by vC.[Guid]) as seq 
)t
where seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SarahBrochu
Starting Member

13 Posts

Posted - 2010-09-02 : 10:32:42

SET ROWCOUNT 0

SELECT vC.[Name], OS1.[Name] As 'Application Name'
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,row_number() over (partition by vC.[Name], OS1.[Name] order by vC.[Guid]) as seq )t
where seq=1

Error:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Publisher" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Hidden" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Version" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Guid" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Guid" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.

matty
Posting Yak Master

161 Posts

Posted - 2010-09-03 : 04:59:08

[code]

SELECT vC.[Name], OS1.[Name] As 'Application Name'
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,
row_number() over (partition by vC.[Name], OS1.[Name]
order by vC.[Guid]) as seq
FROM vComputer vC
Join dbo.Inv_AeX_OS_Add_Remove_Programs OS1 ON OS1.[_ResourceGuid]= Vc.[Guid]

)t
where seq=1

[/code]

quote:
Originally posted by SarahBrochu

SET ROWCOUNT 0

SELECT vC.[Name], OS1.[Name] As 'Application Name'
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,row_number() over (partition by vC.[Name], OS1.[Name] order by vC.[Guid]) as seq )t
where seq=1

Error:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Publisher" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Hidden" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Version" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Guid" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Guid" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.

SarahBrochu
Starting Member

13 Posts

Posted - 2010-09-07 : 17:04:17

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 05:08:43

quote:
Originally posted by SarahBrochu

SET ROWCOUNT 0

SELECT vC.[Name], OS1.[Name] As 'Application Name'
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,
row_number() over (partition by vC.[Name], OS1.[Name]
order by vC.[Guid]) as seq
FROM vComputer vC
Join dbo.Inv_AeX_OS_Add_Remove_Programs OS1 ON OS1.[_ResourceGuid]= Vc.[Guid])t

where seq=1

I get this error and don't know how to fix
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "vC.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "OS1.Name" could not be bound.

Above is all messy.Try this


SELECT vC.[Name], OS1.[Name] As 'Application Name'
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,
row_number() over (partition by vC.[Name], OS1.[Name] order by vC.[Guid] )as seq 
FROM vComputer vC 
Join dbo.Inv_AeX_OS_Add_Remove_Programs OS1 ON OS1.[_ResourceGuid]= Vc.[Guid] 


)t
where seq=1

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

SarahBrochu
Starting Member

13 Posts

Posted - 2010-09-08 : 12:18:57

There is not much of a difference
with this query
SET ROWCOUNT 0

SELECT vC.[Name], OS1.[Name] As 'Application Name'
from
(
SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid] ,
row_number() over (partition by vC.[Name], OS1.[Name]
order by vC.[Guid]) as seq
FROM vComputer vC
Join dbo.Inv_AeX_OS_Add_Remove_Programs OS1 ON OS1.[_ResourceGuid]= Vc.[Guid])t

where seq=1

Can some one help me with this please?

I am trying to get the Unique Rows for Name field and ApplicationName Field only.

If I used Distinct it applies to all the fields. I only want Name and ApplicationName to return unique fields.

here is my script:

SET ROWCOUNT 0

SELECT vC.[Name], OS1.[Name] As 'Application Name', OS1.[Publisher], OS1.[Hidden], OS1.[Version]
,vC.[Guid]
FROM vComputer vC
Join dbo.Inv_AeX_OS_Add_Remove_Programs OS1 ON OS1.[_ResourceGuid]= Vc.[Guid]
WHERE OS1.[Install Path] NOT LIKE 'C:\WINDOWS%'
AND OS1.[Hidden]='False'
AND OS1.[Install Path] NOT LIKE '%Altiris%'
AND OS1.[Install Path] NOT LIKE '%ATI%'
AND OS1.[Install Path] NOT LIKE 'C:\WINDOWS%'
AND OS1.[Uninstall Path] NOT LIKE '%Uninstall%'
AND OS1.[Publisher] Not Like '%Altiris%'
AND OS1.[_id] Not Like '134%'
AND OS1.[Name] Not Like '%MSXML%'
AND OS1.[Name] Not Like '%Update%'
And OS1.[Name] NOT Like '%Update%'
And OS1.[Name] Not Like '%Security%'
And OS1.[Name] Not Like '%Hotfix%'
And OS1.[Name] Not Like '%Hot fix%'
And OS1.[Name] Not Like '%driver%'
And OS1.[Name] Not Like '%help%'
And OS1.[Name] <> 'HighMAT Extension to Microsoft Windows XP CD Writing Wizard'
AND OS1.[Name] <> 'infocentral'
AND OS1.[Name] Not Like '%spelling dictionaries%'
AND OS1.[Name] Not Like '%care pack%'
AND OS1.[Name] Not Like '%HP Color%'
AND OS1.[Name] Not Like '%HP Deskjet%'
AND OS1.[Name] Not Like '%HP Laserjet%'
AND OS1.[Name] Not Like '%HP Officejet%'
AND OS1.[Name] Not Like '%HP Scanjet%'
AND OS1.[Name] Not Like '%fonts%'
AND OS1.[Name] Not Like 'Components Installer'
AND OS1.[Name] Not Like '%Altiris%'
AND OS1.[Name] Not Like '%Conexant%'
AND OS1.[Name] Not Like '%ATI%'
AND OS1.[Name] Not Like 'Microsoft .NET%'
AND OS1.[Name] Not Like '%Internet Explorer%'
AND OS1.[Name] Not Like '%(KB%'
AND OS1.[Name] Not Like '%Compatibility Pack for the 2007 Office System%'
Order by Publisher, [Application Name], vC.[Name]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-08 : 12:42:04

[code]SELECT
Name,
[Application Name],
Publisher,
Hidden,
[Version],
[Guid]
FROM
(
SELECT
vC.[Name],
OS1.[Name] As 'Application Name',
OS1.[Publisher],
OS1.[Hidden],
OS1.[Version],
vC.[Guid],
ROW_NUMBER() OVER (PARTITION BY vC.Name, OS1.Name ORDER BY vC.Name) AS RowNum
FROM
vComputer AS vC
Join
dbo.Inv_AeX_OS_Add_Remove_Programs AS OS1
ON OS1.[_ResourceGuid]= Vc.[Guid]
WHERE
OS1.[Install Path] NOT LIKE 'C:\WINDOWS%'
AND OS1.[Hidden]='False'
AND OS1.[Install Path] NOT LIKE '%Altiris%'
AND OS1.[Install Path] NOT LIKE '%ATI%'
AND OS1.[Install Path] NOT LIKE 'C:\WINDOWS%'
AND OS1.[Uninstall Path] NOT LIKE '%Uninstall%'
AND OS1.[Publisher] Not Like '%Altiris%'
AND OS1.[_id] Not Like '134%'
AND OS1.[Name] Not Like '%MSXML%'
AND OS1.[Name] Not Like '%Update%'
And OS1.[Name] NOT Like '%Update%'
And OS1.[Name] Not Like '%Security%'
And OS1.[Name] Not Like '%Hotfix%'
And OS1.[Name] Not Like '%Hot fix%'
And OS1.[Name] Not Like '%driver%'
And OS1.[Name] Not Like '%help%'
And OS1.[Name] <> 'HighMAT Extension to Microsoft Windows XP CD Writing Wizard'
AND OS1.[Name] <> 'infocentral'
AND OS1.[Name] Not Like '%spelling dictionaries%'
AND OS1.[Name] Not Like '%care pack%'
AND OS1.[Name] Not Like '%HP Color%'
AND OS1.[Name] Not Like '%HP Deskjet%'
AND OS1.[Name] Not Like '%HP Laserjet%'
AND OS1.[Name] Not Like '%HP Officejet%'
AND OS1.[Name] Not Like '%HP Scanjet%'
AND OS1.[Name] Not Like '%fonts%'
AND OS1.[Name] Not Like 'Components Installer'
AND OS1.[Name] Not Like '%Altiris%'
AND OS1.[Name] Not Like '%Conexant%'
AND OS1.[Name] Not Like '%ATI%'
AND OS1.[Name] Not Like 'Microsoft .NET%'
AND OS1.[Name] Not Like '%Internet Explorer%'
AND OS1.[Name] Not Like '%(KB%'
AND OS1.[Name] Not Like '%Compatibility Pack for the 2007 Office System%'
) AS T
WHERE
RowNum = 1
ORDER BY
Publisher,
[Application Name],
[Name][/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources