| Most common record [message #298093] |
Tue, 05 February 2008 00:28  |
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 #298279 is a reply to message #298103] |
Tue, 05 February 2008 09:42  |
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 #298399 is a reply to message #298093] |
Wed, 06 February 2008 00:57 |
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,
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
