Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ 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
View previous topic :: :: View next topic  
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

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
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

Senior Member


Joined: 11 Aug 2009
Posts: 1281
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts SORT JSON type of data maxsubrat DFSORT/ICETOOL 8 Wed Apr 19, 2017 6:01 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us