Prev: FYI: IM Consumability Survey
Next: Question: restore from backup in relative path...
From: Arun Srinivasan on 26 Jun 2008 17:33
I have a table that is based on a client_cd,
client_cd, client_nm ..........
It has 1.7 billion rows with varying cardinality for each client.

All I want to do is to set up a sample table having exactly 100 rows
for each client_cd s. I have tried recursion but have problems with
sets (cannot use fetch first 100 rows only and union all inside a
CTE).
Has anyone attempted this? if yes, please help.
From: Dave Hughes on 26 Jun 2008 19:15
Arun Srinivasan wrote:

> I have a table that is based on a client_cd,
> client_cd, client_nm ..........
> It has 1.7 billion rows with varying cardinality for each client.
>
> All I want to do is to set up a sample table having exactly 100 rows
> for each client_cd s. I have tried recursion but have problems with
> sets (cannot use fetch first 100 rows only and union all inside a
> CTE).
> Has anyone attempted this? if yes, please help.

Hmm, you /can/ use FETCH FIRST and UNION ALL within CTEs as the former
is a clause of subselect and the latter of full-select on DB2 for LUW
(and DB2 9 for z/OS; although in DB2 8 for z/OS you couldn't as FETCH
FIRST is a clause of select-statement there).

Still, I think there's a simpler way with OLAP functions. Assuming your
table is called BIGTABLE ...

WITH T1 AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CLIENT_CD) AS ROWNUM,
T.*
FROM BIGTABLE T
)
SELECT * FROM T1
WHERE ROWNUM <= 100;


Cheers,

Dave.
 | 
Pages: 1
Prev: FYI: IM Consumability Survey
Next: Question: restore from backup in relative path...