|
View previous topic :: View next topic
|
| Author |
Message |
Venkat A
New User
Joined: 01 May 2008
Posts: 2
Location: INDIA
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Venkat A
New User
Joined: 01 May 2008
Posts: 2
Location: INDIA
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I only query on sysibm tables to prove it works.
thx for the feedback. |
|
| Back to top |
|
|
|
|
