Home » SQL & PL/SQL » SQL & PL/SQL » Most common record
Most common record [message #298093] Tue, 05 February 2008 00:28 Go to next message
uicmxz
Messages: 48
Registered: July 2006
Member
How I can select most common record in query. For example, below is the result of my query, but I have to return only one record of two.

seq qty num_of_occr
18 20 13
18 15 6

The most common record will have num_of_occur equal 13.

Re: Most common record [message #298101 is a reply to message #298093] Tue, 05 February 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Most common record [message #298103 is a reply to message #298093] Tue, 05 February 2008 00:44 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
may you post your query?

regards,
Re: Most common record [message #298279 is a reply to message #298103] Tue, 05 February 2008 09:42 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
Here is my query:

SELECT
SEQ_NO,
QTY,
count(CL_IND)
FROM
MO_CLAIM,
DATE_ADJ,
CLIENT_GROUP,
MO_DRUGS
WHERE
MO_CLAIM.PRODUCT_ID=MO_DRUGS.PRODUCT_ID
AND DATE_ADJ.SERVICE_DATE=MO_CLAIM.SERVICE_DATE
AND MO_CLAIM.CLIENT_ID=CLIENT_GROUP.CLIENT_ID
AND CLIENT_GROUP.CLIENT_ID = '2167'
AND DATE_ADJ.YEAR_NB = 2005
AND DATE_ADJ.MONTH_NB = 3
GROUP BY
SEQ_NO,
QTY,
having count(CL_IND) > 1
order by SEQ_NO ,
count(CL_IND) desc

Here is result:
SEQ_NO QTY count(CL_IND)
18 30 13
18 90 6

I have to return only first record with the most common CL_IND.
Re: Most common record [message #298285 is a reply to message #298279] Tue, 05 February 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my post.

Regards
Michel
Re: Most common record [message #298310 is a reply to message #298285] Tue, 05 February 2008 11:45 Go to previous messageGo to next message
uicmxz
Messages: 48
Registered: July 2006
Member
I cannot see your post
Re: Most common record [message #298322 is a reply to message #298310] Tue, 05 February 2008 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't see that:
Michel Cadot wrote on Tue, 05 February 2008 07:39
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel


Re: Most common record [message #298358 is a reply to message #298093] Tue, 05 February 2008 22:33 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
add it to your code
where rownum>=1


regards,
Re: Most common record [message #298390 is a reply to message #298358] Wed, 06 February 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is this advice?
rownum is ALWAYS >= 1 (unless there is no row to return).
So why should he add this?

Regards
Michel
Re: Most common record [message #298399 is a reply to message #298093] Wed, 06 February 2008 00:57 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
oh! i written it wrong .

my advice was to select first of the row in which count(CL_IND) will be maximum always as count(CL_IND) is in desc oreder and
only maximum value row is required .

it should rownum<=1.

regards,
Re: Most common record [message #298405 is a reply to message #298399] Wed, 06 February 2008 01:09 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I did not check for the validity of your solution, but rownum <= 1 should be rownum = 1
Rownum < 1 does not make sense.
Previous Topic: memory allocation of associative arrays
Next Topic: PLS-00103
Goto Forum:
  


Current Time: Tue Dec 06 10:02:30 CST 2016

Total time taken to generate the page: 0.18193 seconds