Finding primary occurance of specific item in many

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 2005 Forums

Transact-SQL (2005)

Finding primary occurance of specific item in many

Author

Topic

madlinux
Starting Member

5 Posts

Posted - 2009-01-26 : 19:35:32

System that collects login information for users to network. Everytime a user logins in the username and computer name are written to a database. In a view I can link the logon tbl to the corresponding workstation tbl and see the rows match sucessfully but since the logon information is captured every login I see many duplicates. What I have been trying to do is create my view to only display the username to the computer that they have logged into the most. I am still at the "SQL Learning Stage" so I feel that it is neccessary to post a request for a little "bump" in the right direction so I can find this information this year :-) In advance any help would be greatly apprecaited.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-26 : 22:39:09

Please post sample data and expected output.

madlinux
Starting Member

5 Posts

Posted - 2009-01-26 : 23:14:59

SamAccount CompName
_________________________
Adam COMP1
Adam COMP1
Adam COMP1
Adam COMP2
Bob COMP3
Bob COMP3
Bob COMP4
Bob COMP4
Bob COMP4
Dan COMP5
Dan COMP6
Dan COMP6
Dan COMP6
Dan COMP6
Dan COMP6

______________________

Expected Result:

Adam COMP1
Bob COMP4
Dan COMP6

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-26 : 23:23:44

declare @temp table ( SamAccount varchar(32),CompName varchar(32))
insert into @temp
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP2' union all
select 'Bob', 'COMP3' union all
select 'Bob', 'COMP3' union all
select 'Bob', 'COMP4' union all
select 'Bob', 'COMP4' union all
select 'Bob', 'COMP4' union all
select 'Dan', 'COMP5' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6'

select samaccount,compname from
( select *, row_number() over ( partition by samaccount order by compname) as rn from @temp ) t
where rn = 3

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-26 : 23:30:30

Hi Dear,

Is this something you expected..

Code
DECLARE @TEST TABLE(SamAccount varchar(50),CompName varchar(50))
INSERT INTO @TEST
SELECT 'Adam','COMP1' UNION ALL
SELECT 'Adam','COMP1' UNION ALL
SELECT 'Adam','COMP1' UNION ALL
SELECT 'Adam','COMP2' UNION ALL
SELECT 'Bob','COMP3' UNION ALL
SELECT 'Bob','COMP3' UNION ALL
SELECT 'Bob','COMP4' UNION ALL
SELECT 'Bob','COMP4' UNION ALL
SELECT 'Bob','COMP4' UNION ALL
SELECT 'Dan','COMP5' UNION ALL
SELECT 'Dan','COMP6' UNION ALL
SELECT 'Dan','COMP6' UNION ALL
SELECT 'Dan','COMP6' UNION ALL
SELECT 'Dan','COMP6' UNION ALL
SELECT 'Dan','COMP6'

SELECT * FROM @TEST

SELECT SamAccount,MAX(CompName) FROM @TEST
GROUP BY SamAccount

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-26 : 23:35:54

Don't u Have any audit columns in your table like Identity column or Datecolumn ???

madlinux
Starting Member

5 Posts

Posted - 2009-01-26 : 23:42:42

I do have columns that show date, not to sure what you mean by identity.

There is one item I did not mention, there are 5 million rows in the logon table. Is it feasible to use the examples above in this situation?

I am going to try both of the helpful responses on a small example of what I am trying to accomplish to see if I can understand the logic better. Thanks :-)

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-26 : 23:46:58

IDENTITY Column is nothing but a column in which the value(integer) is autogenerated by the SQL to maintain the uniqueness of a table.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 00:30:46

quote:
Originally posted by madlinux

I do have columns that show date, not to sure what you mean by identity.

There is one item I did not mention, there are 5 million rows in the logon table. Is it feasible to use the examples above in this situation?

I am going to try both of the helpful responses on a small example of what I am trying to accomplish to see if I can understand the logic better. Thanks :-)

is it an audit field like datemodified (one which gets autoupdated on insert/updates?). then this will be enough


select samaccount,compname from 
( select *, row_number() over ( partition by samaccount order by datecol desc) as rn from @temp ) t
where rn = 1

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-27 : 00:34:35

Hi try this

DECLARE @TEST TABLE(SamAccount varchar(50),CompName varchar(50))
INSERT INTO @TEST
SELECT 'Adam','COMPt' UNION ALL
SELECT 'Adam','COMPt' UNION ALL
SELECT 'Adam','COMP1' UNION ALL
SELECT 'Adam','COMP2' UNION ALL
SELECT 'Bob','COMPy' UNION ALL
SELECT 'Bob','COMPx' UNION ALL
SELECT 'Bob','COMPx' UNION ALL
SELECT 'Bob','COMPx' UNION ALL
SELECT 'Bob','COMP4' UNION ALL
SELECT 'Dan','COMP5' UNION ALL
SELECT 'Dan','COMP6' UNION ALL
SELECT 'Dan','COMP6' UNION ALL
SELECT 'Dan','COMP9' UNION ALL
SELECT 'Dan','COMP9' UNION ALL
SELECT 'Dan','COMP9'

select distinct t1.samaccount,t1.compname from @test t1
inner join
( select row_number() over ( partition by samaccount order by count(compname) desc) as rn,compname from @test group by samaccount,compname ) t
on t.compname = t1.compname and t.rn = 1

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-27 : 00:39:09

quote:
Originally posted by visakh16

quote:
Originally posted by madlinux

I do have columns that show date, not to sure what you mean by identity.

There is one item I did not mention, there are 5 million rows in the logon table. Is it feasible to use the examples above in this situation?

I am going to try both of the helpful responses on a small example of what I am trying to accomplish to see if I can understand the logic better. Thanks :-)

is it an audit field like datemodified (one which gets autoupdated on insert/updates?). then this will be enough

select samaccount,compname from
( select *, row_number() over ( partition by samaccount order by datecol desc) as rn from @temp ) t
where rn = 1

Hi Visakh,

I think what MadLinux wants is Username and companyname which is occured max times in his table...Isn't it...? I guessed it from His Expected Result..

Kokkula
Starting Member

41 Posts

Posted - 2009-01-28 : 06:07:16

Hello,

Try this

declare @temp table ( SamAccount varchar(32),CompName varchar(32))
insert into @temp

select 'Adam', 'COMP1' union all
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP2' union all
select 'Bob', 'COMP3' union all
select 'Bob', 'COMP3' union all
select 'Bob', 'COMP4' union all
select 'Bob', 'COMP4' union all
select 'Bob', 'COMP4' union all
select 'Dan', 'COMP5' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6'

SELECT * FROM @Temp

SELECT A.SamAccount, A.CompName
FROM @Temp A
GROUP BY SamAccount, CompName
HAVING COUNT(1) = (SELECT MAX(Cnt)
FROM
(
SELECT SamAccount, CompName, COUNT(1) AS Cnt
FROM @Temp
GROUP BY SamAccount, CompName)B
WHERE A.SamAccount = B.SamAccount
)

--
Thanks,
Pavan

madlinux
Starting Member

5 Posts

Posted - 2009-02-02 : 16:29:21

Hello, back to this one - I have been trying to understand how I can use the identity column to my advantage, like I said there is almost 5 million rows in the table so wrapping each row in a SELECT statement seems not the way to go. Can someone provide an example of using a identity column to "loop" through?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 09:33:23

quote:
Originally posted by madlinux

Hello, back to this one - I have been trying to understand how I can use the identity column to my advantage, like I said there is almost 5 million rows in the table so wrapping each row in a SELECT statement seems not the way to go. Can someone provide an example of using a identity column to "loop" through?

why do you want to loop through? wont a batch executing query (set based) be enough?

madlinux
Starting Member

5 Posts

Posted - 2009-02-04 : 01:21:51

quote:
Originally posted by Nageswar9

declare @temp table ( SamAccount varchar(32),CompName varchar(32))
insert into @temp
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP1' union all
select 'Adam', 'COMP2' union all
select 'Bob', 'COMP3' union all
select 'Bob', 'COMP3' union all
select 'Bob', 'COMP4' union all
select 'Bob', 'COMP4' union all
select 'Bob', 'COMP4' union all
select 'Dan', 'COMP5' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6' union all
select 'Dan', 'COMP6'

select samaccount,compname from
( select *, row_number() over ( partition by samaccount order by compname) as rn from @temp ) t
where rn = 3

This was what I needed, after banging my head on the wall (remember I am a SQL noob) I realized that

SELECT SamAccount,MAX(CompName) FROM @TEST
GROUP BY SamAccount

Worked like I needed it to, funny when one over complicates a problem and the answer was right in front of them :-) Thanks for everyone's help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 03:12:48

this will just return you alphabetically latest company name linked to account,is that waht you were looking at?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources