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

ongwc
ongwc used Ask the Experts™
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

Which return follow result:
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
...

Thank you for your time...
Comment
Watch Question

Do more with

Expert Office
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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial