Select rows WHERE there are multiples of one Fiel

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

Transact-SQL (2012)

Select rows WHERE there are multiples of one Fiel

Author

Topic

12many
Starting Member

9 Posts

Posted - 2013-04-10 : 08:46:55

Hi there
I Have this Table

Id Time Name
1 08:00 Ian
2 08:05 Lindsey
3 08:10 Sally
4 08:15 Lisa
5 08:20 Ian

How can i Select only the ons that say Ian

So Far i have this

"SELECT * FROM tbl HAVING COUNT(Name) > 1" this is incorrect,I believe, Because i don't have Count(Name) in the main query

Many thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-10 : 08:48:30

Select name,count(1)
From yourTableName
Group BY name
Having count(1)>1

Cheers
MIK

12many
Starting Member

9 Posts

Posted - 2013-04-10 : 09:40:01

Hi Mik

Thanks for your help Sadly that didn't work for me, i need the 'Id' as Well If i do that all the counts come out as one

The idea of what im trying to do is this For 'Ian' he has two records one early in the morning and one later i want to be able to mark the Later one as it is not needed

My desired Out Put Would be something like

ID Time Name
1 08:00 Ian
5 08:20 Ian

Many Thanks Ian

Regards Ian

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-10 : 10:41:37

[code]select ID, Time, Name
from
(
select *,
COUNT(*) over (partition by Name) as N
from
yourTableName
) s where N > 1;[/code]

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-04-10 : 18:18:08

SELECT * FROM Table where Name = 'Ian'

Your question does not mention counting the entries

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-10 : 18:29:06

quote:
Originally posted by UnemployedInOz

SELECT * FROM Table where Name = 'Ian'

Your question does not mention counting the entries

True, but the subject line asks for "selecting rows where there are multiples of one Field"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:31:12

[code]
SELECT ID,Time,Name
FROM
(
SELECT *,COUNT(1) OVER (PARTITION BY Name) AS Occ
FROM Table
)t
WHERE Occ>1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

12many
Starting Member

9 Posts

Posted - 2013-04-12 : 03:17:26

quote:

True, but the subject line asks for "selecting rows where there are multiples of one Field"

Cheers james k totally correct ;)

Hi Guys thanks for for all the help defiantly given me a good couple of thing too try out

Many thanks indeed :)

Ian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources