Using MAX with group by - 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

General SQL Server Forums

New to SQL Server Programming

Using MAX with group by

Author

Topic

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-11-29 : 19:56:21

Here is my query:
select tFrom, count(1) as WEBFORMCOUNT into #webtemp from tblSync with (nolock)where tFrom not like 'msconus%'group by tFrom order by WEBFORMCOUNT desc
select top 1 tFrom,WEBFORMCOUNT from #webtemp
drop table #webtemp

The above query works but I know it can be acheived in a simpler way.

My result set needs to contain 1 row (2 columns) - 'tFrom' with max(count). I tried something like this.
select max(WEBFORMCOUNT) from ( select WEBFORMCOUNT = count(1) from tblSync with (nolock)
group by tFrom) A

But here, I get max(count) only and not the 'tFrom'. I would need max(count) along with 'tFrom'. Please suggest. Thank you.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-11-29 : 20:40:54

Hi there.
Is this what youre looking for? Anytime you use a TOP operator you should be sure to implement an ORDER BY as well. Otherwise, you are rolling the dice. Notice how I moved your ORDER BY into your 2nd SELECT that uses the TOP.

Anyways, as you mentioned, you can do this in one query.


declare @tblSync table (tID int identity(1,1), tFrom varchar(10))
	
insert into @tblSync
	select 'Nathan' union all
	select 'Nathan' union all
	select 'Nathan' union all
	select 'Arlene' union all
	select 'Arlene' union all
	select 'msconus2'


declare @WebTemp table (tFrom varchar(10), WebFormCount int)

---------------------------------------------------
-- your query
---------------------------------------------------
insert into @WebTemp
select	tFrom, 
		count(1) as WebFormCount 
from	@tblSync
where	tFrom not like 'msconus%'
group 
by		tFrom 
--order 
--by		WebFormCount desc

select	top 1 
		tFrom,
		WebFormCount 
from	@webtemp
order 
by		WebFormCount desc

---------------------------------------------------
-- new query
---------------------------------------------------
select top 1 
		tFrom,
		count(*)
from	@tblSync
where	tFrom not like 'msconus%'
group 
by		tFrom
order
by		count(*) desc

Nathan Skerl

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-11-29 : 21:56:58

Thank you for pointing out the usage of TOP clause. The query works like a charm.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-11-30 : 13:12:54

glad to help

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources