# Can I find the MAX value within GROUP BY... pls help...

on
Hi all,
I have this Stored Procedure:

SELECT area, [group], COUNT([group]) AS total
FROM tbl_Area
WHERE status=0 AND CONVERT(CHAR(10), week_day, 103)=CONVERT(CHAR(10),GETDATE() - 1, 103)
GROUP BY [group], area

area   group   total
----   -----   -----
A      AI      1
A      BS      2
A      SB      1
F      AI      2
F      BS      4
F      SB      2
...

Is there a way to find the MAX value with the grouped recordset just
like below result?

area   group   total
----   -----   -----
A      BS      2
F      BS      4
...

Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
I suspect there has to be a better way, but here is one approach you can take.

First create a View (View1) that contains:
SELECT area, [group], COUNT([group]) AS total
FROM tbl_Area
WHERE status=0 AND CONVERT(CHAR(10), week_day, 103)=CONVERT(CHAR(10),GETDATE() - 1, 103)
GROUP BY [group], area

Than do the following:
Select View1.*
From   View1
Inner Join (
Select     Area,
Max(Total) As MaxTotal
From     View1
Group by Area) A On View1.Area = A.Area And View1.Total = A.MaxTotal
Order By View1.Area

On problem with this approach is that it will include two groups with the same maximum total.

Anthony

Commented:
Select a.*
From
(Select area, [group], Count(*) as Total,
From tbl_area Where status =0 and convert(char(10),week_day,103) = convert(char(10),getdate()-1,103)) a
inner join
(Select c.area, Max(c.Total) as total From
(Select area, [group], Count(*) as Total,
From tbl_area Where status =0 and convert(char(10),week_day,103) = convert(char(10),getdate()-1,103)) c
Group by  c.area) b
on a.area = b.area and a.total = b.total
order by a.area

Do more with