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
 
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 Moving a COMP-3 Variable to a Numeric... ajayachander COBOL Programming 2 Thu Dec 14, 2017 5:46 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm

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