Home » SQL & PL/SQL » SQL & PL/SQL » SQL Max(cnt)
SQL Max(cnt) [message #364495] Mon, 08 December 2008 19:32 Go to next message
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 189 times)
Re: SQL Max(cnt) [message #364501 is a reply to message #364495] Mon, 08 December 2008 20:59 Go to previous messageGo to next message
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 #364737 is a reply to message #364501] Tue, 09 December 2008 20:12 Go to previous messageGo to next message
ygsunilkumar
Messages: 100
Registered: August 2008
Senior Member
Yes. I executed correctly but getting same error. Please help me.
icon4.gif  Re: SQL Max(cnt) [message #364777 is a reply to message #364495] Wed, 10 December 2008 00:39 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi
If you will follow the formatting rules of forum,
It will give better understanding of problem.
Re: SQL Max(cnt) [message #364801 is a reply to message #364777] Wed, 10 December 2008 01:50 Go to previous messageGo to next message
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 #364802 is a reply to message #364801] Wed, 10 December 2008 01:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of Oracle are you getting this error on?
Re: SQL Max(cnt) [message #364845 is a reply to message #364737] Wed, 10 December 2008 04:25 Go to previous message
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

Previous Topic: Splitting string into records
Next Topic: "from-to" sequence from table data
Goto Forum:
  


Current Time: Sun Dec 11 02:02:37 CST 2016

Total time taken to generate the page: 0.11171 seconds