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

Transact-SQL (2005)

SELECT question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-17 : 11:41:35

Got a large table containing the following fields

Username
IPAddress

I want to return all usernames who have > 1 different/distinct IP addresses.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-17 : 11:46:56

select username,count(distinct IPAdress)
from yourtable
group by
username

having count(*) >1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-17 : 11:50:45

[code]Select UserName
from Table1
Group by UserName
Having Count(Distinct IPAddress) > 1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-17 : 11:51:54

Thanks,

The query works but it includes entries with 1 count.

I tried this

SELECT Username, COUNT(DISTINCT IPAddress) AS Expr1
FROM tblAppActions
WHERE (Expr1 > 1)
GROUP BY Username

But it says unknown column Expr1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 11:52:37

Less CPU, same time.

Select UserName
from Table1
Group by UserName
Having min(IPAddress) < max(ipadress)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources