Return top 10 values from each combination of codes from two columns in SQL-CodePudding

For my analysis, I need 10 records from each combination two columns that hold channel and category codes. For example:

|COUNT| Channel_Code | Category_Code |
|————-| ------—————— | ------——————- |
|9526 | ABC          | DEF           |
|4527 | ABC          | JFK           |
|10   | ABC          | 123           |
|912  | WED          | MLK           |
|75   | KJJ          | ONL           |
|1000 | WED          | DEF           |

I only have tried filtering on

WHERE channel_code = ABC 
AND Category_Code = DEF
Sample 10;

Also they using rownum as well, but no luck.

What I’m expecting the output to look like:

|RECORD NUM| Channel_Code | Category_Code |
|—————————-| ------—————— | ------——————- |
|1         | ABC          | DEF           |
|2         | ABC          | DEF           |
|3         | ABC          | DEF           |
|4         | ABC          | DEF           |
|5         | ABC          | DEF           |
|6         | ABC          | DEF           |

Etc… up until the 10th record. Then the next combination will start with 10 records of ABC and JFK

Is there a way to partition this in Teradata SQL? Or another possible solution. Thanks for your help!

CodePudding user response：

You can use row_number as you mentioned:

SELECT
record_num, channel_code, category_code
FROM (SELECT record_num, channel_code, category_code,
ROW_NUMBER over (partition by channel_code, category_code order by record_num asc) as rn
FROM table_name
)
WHERE rn<=10

CodePudding user response：

If you are basically trying to create these rows, you can use a cross join to a simple numbers table.

create volatile table vt_nums
(num integer)
on commit preserve rows;

insert into vt_nums values(1);
insert into vt_nums values(2);
insert into vt_nums values(3);
insert into vt_nums values(4);
insert into vt_nums values(5);

And here's some made up data to join with:

create volatile table vt_foo
(col1 varchar(10))
on commit preserve rows;
insert into vt_foo values ('a');
insert into vt_foo values ('b');

Finally:

select
vt_nums.num,
vt_foo.col1
from
vt_foo
cross join vt_nums
order by
2,1

Which will return:

num col1
1   a
2   a
3   a
4   a
5   a
1   b
2   b
3   b
4   b
5   b