MVSFORUMS.com :: View topic - Query for max occurance row in a table

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

Query for max occurance row in a table

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

vkphani
Intermediate

Joined: 05 Sep 2003
Posts: 483
Topics: 48

Posted: Tue Nov 08, 2005 6:09 am Post subject: Query for max occurance row in a table

Hi,

I have a table as follows:

Code:

Client_id Code

1 A
1 A
1 B
1 A
1 C
2 C
2 B
2 A
2 A
2 D
3 B
3 A
3 A
3 A
3 A

We need to select the Client_id which has the Code value occuring more number of times.

In this case the query should return the Client_id 3 (since it contains Code

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12382
Topics: 75
Location: San Jose

Posted: Tue Nov 08, 2005 6:25 am Post subject:

vkphani,

Try this untested sql

Code:

SELECT CLIENT_ID
,COUNT(*) AS CNT
FROM TABLE
GROUP BY CLIENT_ID
,CODE
ORDER BY CNT DESC
FETCH FIRST 1 ROW ONLY
;

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

vkphani
Intermediate

Joined: 05 Sep 2003
Posts: 483
Topics: 48

Posted: Tue Nov 08, 2005 6:30 am Post subject:

Thanks Kolusu. The query worked fine.

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum