Getting the Max of Counts

[royalcheese]

Hi all , relitivly new to SQL and i have made this so far ....

select   a.project
	, a.department
	, count(a.department)



from atsproject as a
join acrclient as b on b.municipal = 'Y' and b.client = a.client




where     a.project = 'ECP12674'

group by  a.project
	, a.department 

order by  a.project asc 
	, count(a.department) desc

this returns

project dep count of dep
ECP12674 621 21
ECP12674 641 5


What i want to do is to return only the row with the highest count of dep e.g ,

ECP12674 621 21

this is a small section of data with many projects and many dep's , can i get any pointers please ?!

. Many thanks


C

 

[bborissov]

select TOP 1 a.project
           , a.department
           , count(a.department)
from atsproject as a
join acrclient as b on b.municipal = 'Y' and b.client = a.client
where     a.project = 'ECP12674'
group by  a.project
        , a.department
order by  a.project asc
        , count(a.department) desc

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[royalcheese]

apologies , there are many many different combonations of data

current

proj dep Count of dep
5210730 730 26
5210800 800 5
5211442 440 26
5211442 442 29
5211442 442 26 * this one

wanted return


5210730 730 26
5210800 800 5
5211442 440 26
5211442 442 29

the last row* was removed because there is another that has a higher count of dep

C

 

[royalcheese]

use tempdb
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[#temp]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [#temp]

go
use agresso

select a.project, a.department, count(a.department) as countofdep
into dbo.#temp
from atsproject as a
join acrclient as b
on b.municipal = 'Y'
and b.client = a.client
where a.project = 'ECP12674'
group by a.project, a.department

go
Solution ---

use tempdb

SELECT b.*

FROM (SELECT project, MAX(countofdep) AS maxdep
FROM #temp
GROUP BY project ) a INNER JOIN
(SELECT *
FROM #temp) b ON a.maxdep = b.countofdep AND a.project = b.project --and a.department = b.department

 

[bborissov]

SELECT Tbl1.project,
       Tbl1.Departement,
       Tbl1.Cnt
FROM (SELECT Project,
             Departement,
             COUNT(Departement) AS Cnt
      FROM atsproject
      join acrclient on
           atsproject.Client = acrclient.Client AND
           acrclient.municipal = 'Y'
      group by atsproject.project,
               atsproject.department
      ) Tbl1
INNER JOIN (SELECT Project,
                   Departement,
                   MAX(Cnt) AS Cnt
            FROM (SELECT Project,
                         Departement,
                         COUNT(Departement) AS Cnt
                  FROM atsproject
                  join acrclient on
                  atsproject.Client = acrclient.Client AND
                  acrclient.municipal = 'Y'
                  group by atsproject.project,
                           atsproject.department) Tbl2
             group by atsproject.project,
                      atsproject.department) Tbl3
ON Tbl1.Project    = Tbl3.Project    AND
   Tbl1.department = Tbl3.department AND
   Tbl1.Cnt        = Tbl3.Cnt
where    Tbl1.project = 'ECP12674'
order by Tbl1.project asc,
         Tbl1.Cnt desc

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP