SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select rows WHERE there are multiples of one Fiel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

12many
Starting Member

9 Posts

Posted - 04/10/2013 :  08:46:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/10/2013 :  08:48:30  Show Profile  Reply with Quote
Select name,count(1)
From yourTableName
Group BY name
Having count(1)>1

Cheers
MIK
Go to Top of Page

12many
Starting Member

9 Posts

Posted - 04/10/2013 :  09:40:01  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 04/10/2013 :  10:41:37  Show Profile  Reply with Quote
select ID, Time, Name
from
(
	select *,
		COUNT(*) over (partition by Name) as N
	from
		yourTableName
) s where N > 1;
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 04/10/2013 :  18:18:08  Show Profile  Reply with Quote
SELECT * FROM Table where Name = 'Ian'

Your question does not mention counting the entries
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3695 Posts

Posted - 04/10/2013 :  18:29:06  Show Profile  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/11/2013 :  02:31:12  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

12many
Starting Member

9 Posts

Posted - 04/12/2013 :  03:17:26  Show Profile  Reply with Quote

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000