Portal | IBM Manuals | Downloads | Products | Refer | Info | Programs | JCLs | Forum Rules*| Site Map | Mainframe CD 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index
 
Register
 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index FAQ Search Memberlist Usergroups Profile Log in to check your private messages Log in
 
Sorting DB2 data using SQL query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
Author Message
Venkat A

New User


Joined: 01 May 2008
Posts: 2
Location: INDIA
PostPosted: Thu Jul 08, 2010 11:18 pm    Post subject: Sorting DB2 data using SQL query
 Reply with quote
Hi all, need some help in DB2... iam still learning DB2...not so proficient so please excuse if any mistakes.

I have a DB2 table with 2 columns C1 & C2. C1 is a Number datatype and C2 is a VARCHAR.

C1 has unique values and C2 will have duplicates, the table contains millions of records

for example the table may contain data as follows

Code:
C1   C2
----   ----
1   AA
3   AB
9   AC
4   AA
5   AA
6   AD
7   AC
8   AB
2   AA
.
.
.
and so ...on

suppose if the column C1 contains account numbers and C2 conatins SSN data. One SSN can have differen accounts.

I need a SQL query which can group accounts based on SSN, and should report 2 accounts each for the first 10 SSN's encountered.


i executed the below SQL query in SPUFI and got the result, but want to know if there is a better way

Code:
SELECT COL1,COL2
FROM TABLE
WHERE COL2 IN
(SELECT COL2
FROM TABLE
GROUP BY COL2
HAVING COUNT(COL2)=2
FETCH FIRST 10 ROWS ONLY)
ORDER BY COL2
WITH UR;
Back to top
View user's profile Send private message
References
GuyC

Active User


Joined: 11 Aug 2009
Posts: 479
Location: Belgium
PostPosted: Fri Jul 09, 2010 1:41 pm    Post subject:
Reply with quote
your query only reports SSN which have Exactly 2 accounts

Code:
select c.* from
  (select dbname
     from sysibm.systablespace a
    group by dbname
   having count(*) > 1
    fetch first 10 rows only) A
, table (select * from sysibm.systablespace b where a.dbname = b.dbname
         fetch first 2 rows only) C
Back to top
View user's profile Send private message
Venkat A

New User


Joined: 01 May 2008
Posts: 2
Location: INDIA
PostPosted: Sat Jul 10, 2010 8:24 am    Post subject: Thank you GuyC
 Reply with quote
Thank you GuyC....

This query worked exactly as i wanted

Code:
SELECT C.COL1, C.COL2
FROM
(SELECT COL2
FROM Table1 A
GROUP BY COL2
HAVING COUNT(*) > 1
FETCH FIRST 10 ROWS ONLY) A,
TABLE (SELECT * FROM Table1 B
WHERE A.COL2=B.COL2
FETCH FIRST 2 ROWS ONLY) C;


seeing the sysibm tables in your query, first i thought what a complex query it is ..then i understood your intent..thank you very much
Back to top
View user's profile Send private message
GuyC

Active User


Joined: 11 Aug 2009
Posts: 479
Location: Belgium
PostPosted: Mon Jul 12, 2010 1:01 pm    Post subject:
Reply with quote
I only query on sysibm tables to prove it works.

thx for the feedback.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1