SQL Max(cnt) [message #364495] |
Mon, 08 December 2008 19:32  |
ygsunilkumar
Messages: 100 Registered: August 2008
|
Senior Member |
|
|
Please go through the attachement and tell me the solution for the query. I am getting error please help me.
-
Attachment: max(cnt).txt
(Size: 1.27KB, Downloaded 708 times)
|
|
|
Re: SQL Max(cnt) [message #364501 is a reply to message #364495] |
Mon, 08 December 2008 20:59   |
sundarfaq
Messages: 235 Registered: October 2007 Location: Chennai
|
Senior Member |
|
|
Hi,
I have tried same query with same table structure.
I did'nt get any error.
Did u execute correctly?
Thanks,
Michael.
|
|
|
|
|
Re: SQL Max(cnt) [message #364801 is a reply to message #364777] |
Wed, 10 December 2008 01:50   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Seeing as the OP is unwilling to or incapable of showing us the details clearly:
select modified_by,
(case
when modified_by = (select modified_by
from rxc.received_dcis
where clinical_study_id = 40613
and RECEIVED_DCI_STATUS_CODE like 'REMOVED'
and patient like '01%'
group by modified_by
having count(received_dci_id) = (select max(cnt)
from (select modified_by, count(received_dci_id)cnt
from rxc.received_dcis
where clinical_study_id = 40613
and RECEIVED_DCI_STATUS_CODE like 'REMOVED'
and patient like '01%'
group by modified_by)))
then (select max(cnt)
from (select modified_by, count(received_dci_id)cnt
from rxc.received_dcis
where clinical_study_id = 40613
and RECEIVED_DCI_STATUS_CODE like 'REMOVED'
and patient like '01%'
group by modified_by))
end)"Pt 1"
from rxc.received_dcis
group by modified_by
/
then (select max(cnt)
*
ERROR at line 16:
ORA-00979: not a GROUP BY expression
[Updated on: Wed, 10 December 2008 01:51] Report message to a moderator
|
|
|
|
Re: SQL Max(cnt) [message #364845 is a reply to message #364737] |
Wed, 10 December 2008 04:25  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
I am not sure this query will help you. For what it is worth give it a go. I assumed you are using oracle 9i or greater.
select
max(modified_by)
keep(dense_rank first order by count(*) desc),
max(count(*))
from
rxc.received_dcis
where
clinical_study_id = 40613
and
received_dci_status_code like 'REMOVED'
and patient like '01%'
group by modified_by
I just realised the above query will not give you the correct result if there is more than one modified_by shares the same maximum count. Hopefully this should do the trick.
select * from
(
select modified_by, count(*) cnt, max(count(*)) over() max_cnt
from
rxc.receievd_dcis
where
clinical_study_id = 40613
and
received_dci_status_code like 'REMOVED'
and patient like '01%'
group by modified_by
)
where
cnt = max_cnt
Regards
Raj
[Edit: ] Added the analytical form if there is more than one group shares the same maximum count
[Updated on: Wed, 10 December 2008 04:47] Report message to a moderator
|
|
|