Select Max count(*) problem

[MadhanaGopal]

Hi,
I have one requirement.

select fld1 as col1 ,fld2 as col2,max(fld3) as col3 ,
count(*) as col4 from table1 group by fld1,fld2

It gives the result as

col1 col2 col3 col4
------------------------------------------
A B 25 5
A B 15 15
A B 5 20
C D 10 6
C D 12 13
C D 17 18
| | | |
| | | |

I need one row for each fld1,fld2 which has maximum
count(*).
I want result like this.
col1 col2 col3 col4
------------------------------------------
A B 5 20
C D 17 18
| | | |
| | | |
Is it possible to get the result in a single query?
Thanx in Advance. P.Madhana Gopal,
Principal Software Engineer,
LCube Innovative Solns. Pvt. Ltd.,
India.

 

[r937]

there is something funny about your first query, maybe an error crept in while you were disguising the names or simplifying the query

if you really did a GROUP BY fld1,fld2 then there's no way you're going to find more than one row with values A,B

something else is going on...

rudy

http://rudy.ca/

 

[MadhanaGopal]

Sorry Guys,
I made one mistake.This is the correct query.
select fld1 as col1 ,fld2 as col2,max(fld3) as col3 ,
count(fld4) as col4 from table1 group by fld1,fld2,fld4

It gives the result as

col1 col2 col3 col4
------------------------------------------
A B 25 5
A B 15 15
A B 5 20
C D 10 6
C D 12 13
C D 17 18
| | | |
| | | |

I need one row for each fld1,fld2 which has maximum
count(*).
I want result like this.
col1 col2 col3 col4
------------------------------------------
A B 5 20
C D 17 18
| | | |
| | | |
Is it possible to get the result in a single query?
P.Madhana Gopal,
Principal Software Engineer,
LCube Innovative Solns. Pvt. Ltd.,
India.

 

[aalmeida]

I don't think you would be able to get max () and the count () in one query:
The best bet would be use a stored procedure and basicaly do liek this:
Select Distinct fld1 as col1 ,fld2 as col2, 0.00 as Col3, 0.00 as col4 into #A from table 1

update #A set col3 = max(fld3) and col4 = count(*)
from table 1
where fld1 = col1 and fld2 = col2

SELECT #FROM A AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[sunila7]

Hi,

From the resultset u r getting using the SQL, which u got with ur query
"select fld1 as col1 ,fld2 as col2,max(fld3) as col3 ,
count(fld4) as col4 from table1 group by fld1,fld2,fld4"

u should be able to get the data u want with this SQL. I am not sure this will be the best way to do it. Hope this is waht u r looking for

select distinct col1,col2,
(Select Top 1 col3
FROM
(select fld1 as col1 ,fld2 as col2,max(fld3) as col3,
count(fld4) as col4 from table1 group by fld1,fld2,fld4) e1
where e1.col1 = e.col1 and e1.col2=e.col2 order by col4 desc),
(Select Max(col4)
FROM
(select fld1 as col1 ,fld2 as col2,max(fld3) as col3 ,
count(fld4) as col4 from table1 group by fld1,fld2,fld4) e2
where e2.col1 = e.col1 and e2.col2=e.col2)
From (select fld1 as col1 ,fld2 as col2,max(fld3) as col3 ,
count(fld4) as col4 from table1 group by fld1,fld2,fld4) e

Sunil