Maximum Record Count - 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)

Maximum Record Count

Author

Topic

deepsmehta
Starting Member

4 Posts

Posted - 2008-08-04 : 16:05:18

Hi
I have two Columns in 1 Table like Below
Report Name User
----------- ----------
Sales John
Sales Mike
Marketing Richard
Ad Peter
PreSales Mike
Sales John
Marketing Peter
Ad Peter
PreSales John
Sales John
Marketing Richard

In the above table Sales Report has been seen by John 3 times and Mike only once and similalry Marketing 2 times Richard and 1 time by Peter. So my result set should look like this.
Report Name User
----------- ----------
Sales John
Marketing Richard

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 16:18:29

SELECT ReportName, User
FROM YourTable
GROUP BY ReportName, User
HAVING COUNT(*) > 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

deepsmehta
Starting Member

4 Posts

Posted - 2008-08-04 : 16:49:12

It is giving duplicate records

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 20:38:22

Using DISTINCT may help. Eg SELECT DISTINCT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-04 : 20:43:07

My solution can't give duplicate records, so show us the query you tried.

Also, don't use DISTINCT just to remove duplicates. Let's work on your actual problem and then add DISTINCT if needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-04 : 23:05:58

try this:


select	[report name]
	,user
from	(
	select	[report name]
		,user
		,cnt
		,rn = rownumber() over (partition by [report name], user order by cnt desc)
	from	(
		select	[report name]
			,user
			,count(*) cnt
		from	yourTable 
		group by [report name]
			,user
		) d1
	) d2
where	rn = 1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources