Newbie Question - Go easy

I've read through a number of similar questions in the forum, but because I'
m
so new to SQL Language, I didn't see one that related directly to my attempt
at making mine work.
I'm querying an SMS 2003 database that has self-built entries, etc... by SMS
.
I'm looking to query this db and report back 4 fields Netbios_Name0,
User_Name0, Active0, Obsolete0.
Simple enough...
In the result, if the Netbios_Name0 (machine name) has multiple entries in
the SMS db, these show up and give me much higher numbers than is realistic
to report to Directors, etc...
Ex. If there's really only 100 machines in the db, but because of duplicate
records, it says there's 178.....well, that's not unreliable data to report
back.
I'm looking to result all 4 fields of data as it corresponds to each machine
name, BUT filter out any data (and corresponding data) if the netbios_name0
(machine name) shows up more than once.
So, instead of having:
BobsPC BobWilson Active 1 Obsolete 0
BobsPC BobWilson Active 1 Obsolete 0
It would filter out the duplicate and report back:
BobsPC BobWilson Active 1 Obsolete 0
Here's what I have so far, but I'm still getting errors. Not sure where to
go from here and would certainly appriciate code examples if you wouldn't
mind.
Thx !!!!
ESP
--begin
select distinct
v_R_System.Netbios_Name0,
v_R_System.User_Name0,
v_R_System.Active0,
v_R_System.Obsolete0
from v_R_System
where v_R_System.Active0 = 0
and v_R_System.Obsolete0 = 1
and v_R_System.Netbios_Name0 = (select max (v_R_System.Netbios_Name0 )
from v_R_System
where v_R_System.Netbios_name0 = Netbios_Name0 )
--endI'm not sure what you are trying to accomplish with this part of your query:
and v_R_System.Netbios_Name0 =( select max (v_R_System.Netbios_Name0 ) from
v_R_System where v_R_System.Netbios_name0 = Netbios_Name0 )
Using the MAX aggregrate function on something with a character datatype
will give you the machine that is last in alphabetic order. For example
DECLARE @.foo TABLE (Lastname varchar(50))
INSERT INTO @.foo (Lastname) VALUES ('Johnson')
INSERT INTO @.foo (Lastname) VALUES ('Williams')
SELECT MAX(Lastname) FROM @.foo
would return 'Williams', because it is last in alphabetic order.
Try your query without that last part.
--
"ESP" wrote:

> I've read through a number of similar questions in the forum, but because
I'm
> so new to SQL Language, I didn't see one that related directly to my attem
pt
> at making mine work.
> I'm querying an SMS 2003 database that has self-built entries, etc... by S
MS.
> I'm looking to query this db and report back 4 fields Netbios_Name0,
> User_Name0, Active0, Obsolete0.
> Simple enough...
> In the result, if the Netbios_Name0 (machine name) has multiple entries in
> the SMS db, these show up and give me much higher numbers than is realisti
c
> to report to Directors, etc...
> Ex. If there's really only 100 machines in the db, but because of duplicat
e
> records, it says there's 178.....well, that's not unreliable data to repo
rt
> back.
> I'm looking to result all 4 fields of data as it corresponds to each machi
ne
> name, BUT filter out any data (and corresponding data) if the netbios_name
0
> (machine name) shows up more than once.
> So, instead of having:
> BobsPC BobWilson Active 1 Obsolete 0
> BobsPC BobWilson Active 1 Obsolete 0
> It would filter out the duplicate and report back:
> BobsPC BobWilson Active 1 Obsolete 0
> Here's what I have so far, but I'm still getting errors. Not sure where to
> go from here and would certainly appriciate code examples if you wouldn't
> mind.
> Thx !!!!
> ESP
> --begin
> select distinct
> v_R_System.Netbios_Name0,
> v_R_System.User_Name0,
> v_R_System.Active0,
> v_R_System.Obsolete0
> from v_R_System
> where v_R_System.Active0 = 0
> and v_R_System.Obsolete0 = 1
> and v_R_System.Netbios_Name0 = (select max (v_R_System.Netbios_Name0 )
> from v_R_System
> where v_R_System.Netbios_name0 = Netbios_Name0 )
> --end
>|||I don't say anything syntactically wrong with your statement.
select distinct
max(v_R_System.Netbios_Name0),
v_R_System.User_Name0,
v_R_System.Active0,
v_R_System.Obsolete0
from v_R_System
where v_R_System.Active0 = 0
and v_R_System.Obsolete0 = 1
group by v_R_System.User_Name0,
v_R_System.Active0,
v_R_System.Obsolete0
Will this do what you are looking for?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ESP" <ESP@.discussions.microsoft.com> wrote in message
news:E8FCAD16-5D0F-46D7-A419-5CA42F20BA33@.microsoft.com...
> I've read through a number of similar questions in the forum, but because
> I'm
> so new to SQL Language, I didn't see one that related directly to my
> attempt
> at making mine work.
> I'm querying an SMS 2003 database that has self-built entries, etc... by
> SMS.
> I'm looking to query this db and report back 4 fields Netbios_Name0,
> User_Name0, Active0, Obsolete0.
> Simple enough...
> In the result, if the Netbios_Name0 (machine name) has multiple entries in
> the SMS db, these show up and give me much higher numbers than is
> realistic
> to report to Directors, etc...
> Ex. If there's really only 100 machines in the db, but because of
> duplicate
> records, it says there's 178.....well, that's not unreliable data to
> report
> back.
> I'm looking to result all 4 fields of data as it corresponds to each
> machine
> name, BUT filter out any data (and corresponding data) if the
> netbios_name0
> (machine name) shows up more than once.
> So, instead of having:
> BobsPC BobWilson Active 1 Obsolete 0
> BobsPC BobWilson Active 1 Obsolete 0
> It would filter out the duplicate and report back:
> BobsPC BobWilson Active 1 Obsolete 0
> Here's what I have so far, but I'm still getting errors. Not sure where to
> go from here and would certainly appriciate code examples if you wouldn't
> mind.
> Thx !!!!
> ESP
> --begin
> select distinct
> v_R_System.Netbios_Name0,
> v_R_System.User_Name0,
> v_R_System.Active0,
> v_R_System.Obsolete0
> from v_R_System
> where v_R_System.Active0 = 0
> and v_R_System.Obsolete0 = 1
> and v_R_System.Netbios_Name0 = (select max (v_R_System.Netbios_Name0 )
> from v_R_System
> where v_R_System.Netbios_name0 = Netbios_Name0 )
> --end
>|||Thank you very much for your responses. Although both examples do return
results, there are still duplicate results of the same thing.
Goal:
Instead of having this as a result:
BobsPC BobWilson Active 1 Obsolete 0
BobsPC BobWilson Active 1 Obsolete 0
It would filter out the duplicate and report back this result:
BobsPC BobWilson Active 1 Obsolete 0
Thx !!!!!
ESP
p.s. My alias is ESP, but my name is Mark Williams as well
"Louis Davidson" wrote:

> I don't say anything syntactically wrong with your statement.
> select distinct
> max(v_R_System.Netbios_Name0),
> v_R_System.User_Name0,
> v_R_System.Active0,
> v_R_System.Obsolete0
> from v_R_System
> where v_R_System.Active0 = 0
> and v_R_System.Obsolete0 = 1
> group by v_R_System.User_Name0,
> v_R_System.Active0,
> v_R_System.Obsolete0
> Will this do what you are looking for?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "ESP" <ESP@.discussions.microsoft.com> wrote in message
> news:E8FCAD16-5D0F-46D7-A419-5CA42F20BA33@.microsoft.com...
>
>|||Can you post some sample data and the query that gives the invalid results?
I don't know how this is possible, and it is too hard to guess without
seeing data.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ESP" <ESP@.discussions.microsoft.com> wrote in message
news:308CD777-66CA-46FC-AF8F-D8655D2DDDC9@.microsoft.com...
> Thank you very much for your responses. Although both examples do return
> results, there are still duplicate results of the same thing.
> Goal:
> Instead of having this as a result:
> BobsPC BobWilson Active 1 Obsolete 0
> BobsPC BobWilson Active 1 Obsolete 0
> It would filter out the duplicate and report back this result:
> BobsPC BobWilson Active 1 Obsolete 0
>
> Thx !!!!!
> ESP
> p.s. My alias is ESP, but my name is Mark Williams as well
>
>
> "Louis Davidson" wrote:
>