Pick rows with certain conditions

General
Enthusiast

Pick rows with certain conditions

Hi All,

 

I have requirement here to pick rows on certain condition. PFB the input and desired output.

Conditions: The row is valid if str type = 'S' and the item should not have str_type 'A' & 'D' associated.

Input:

ItemStr_typecount
1A12
1D34
2A56
2D35
2S12
3S1
4S7

 

Output:

ItemStr_typecount
3S1
4S7

 

Thanks for helping!

 

Skn

3 REPLIES
Teradata Employee

Re: Pick rows with certain conditions

try this:

 

SELECT DISTINCT item, str_type, num

FROM test

WHERE str_type = 'S'

AND item NOT IN (SELECT item FROM TEST WHERE str_type IN ('A','D'));

Enthusiast

Re: Pick rows with certain conditions

Thanks for your reply. But this will be very slow. My table has 17 Billion rows. I'm looking for something optimized.

 

Teradata Employee

Re: Pick rows with certain conditions

Conceptual - not tested...

This will be executed as a single table scan with aggregation - best that can be done unless other conditions can be applied to reduce the incoming set from the table. If there are other str_types for example they could be eliminated with an appropriate where clause.

This will not work properly if there are multiple S rows for an item since it will add the counts together. I added a counter to locate any cases where this is the case.

S_Count has been used to name the "count" column in the table supplied since "count" is a reserved word.

 

SELECT item,

  'S', 

  SUM(CASE WHEN str_type='S' THEN S_count ELSE 0) AS item_count,

  SUM(CASE WHEN str_type='S' THEN 1 ELSE 0) AS S_item_row_count

 

FROM table

GROUP BY item

HAVING

  SUM(CASE WHEN str_type='S' THEN 1 ELSE 0) > 0 AND

  SUM(CASE WHEN str_type='A' OR str_type='D' THEN 1 ELSE 0) = 0;