Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
count of largest n of recs with the same customer in a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
sathish_rathinam

New User


Joined: 22 Aug 2005
Posts: 59
Location: india

PostPosted: Thu Jun 26, 2008 4:19 pm    Post subject: count of largest n of recs with the same customer in a table
Reply with quote

In a table,there are 3,00,000 records and it has customer id field as one of its fields.
I want to pick up the customer id which is present in large number than all other customer id's using a SQL.
Back to top
View user's profile Send private message

Antonio Barata
Warnings : 1

New User


Joined: 04 Apr 2007
Posts: 37
Location: Lisbon, Portugal

PostPosted: Thu Jun 26, 2008 5:41 pm    Post subject: Reply to: count of largest n of recs with the same customer
Reply with quote

Hi

Select cust_id, count(*)
from TABLE_NAME
group by cust_id

It will give you a list with the customer id's and the number of times they occur within the table.
Notice that this is a very expensive SQL statement. It has to perform a full table space scan in order to obtain the data.
Back to top
View user's profile Send private message
ssk1711

New User


Joined: 16 Jun 2008
Posts: 40
Location: bangalore

PostPosted: Thu Jun 26, 2008 6:33 pm    Post subject:
Reply with quote

hi sathish,

try this..

********
select count(column_name) col1,column_name
from table_name
group by column_name
order by col1 desc
fetch first 1 row only
***********
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us