How to force output to show only 1 row if multiple values appear

Question

I have the following query:

SELECT 
    item_code,
    description_global,
    dept_item || class1_item AS division,
    view_year_code AS year,
    ssn AS season
FROM 
    us_raw.l_ims_sum_code_master
WHERE 
    ssn IN (3,4)
    AND view_year_code = 9
    AND description_global IS NOT NULL
    AND item_code = 418251

Here's what it looks like in the output:

I only want my data to pull with one of those description_global values. I know I can just do LIMIT 1, but I need another way to do it because this is just one example, and I want my output to force only 1 row for each time there are multiple description_global values.

Thank you, Z

score 0 · Answer 1 · answered Nov 27 '19 at 22:56

You can use window functions:

SELECT *
FROM (SELECT item_code, description_global, dept_item || class1_item AS division,
             view_year_code AS year, ssn AS season,
             ROW_NUMBER() OVER (PARTITION BY item_code ORDER BY item_code) as seqnum
      FROM us_raw.l_ims_sum_code_master
      WHERE ssn IN (3,4) AND
            view_year_code = 9 AND
            description_global IS NOT NULL AND
            item_code = 418251
     ) scm
WHERE seqnum = 1;

score 0 · Accepted Answer · answered Dec 06 '19 at 15:28

I was able to resolve this issue by using an Index in Tableau. Here are the steps for anyone that is using SQL+Tableau:

1.) Create a calculated field
2.) Simply enter Index()
3.) Click OK
4.) Drag the newly created field into your rows
5.) Right-click on the Index pill (whatever you had named it) and click compute on
6.) Select whichever field that has multiple values (item description for me)
7.) Right-click again and click filter
8.) Filter to only show 1s
9.) Remove from rows (if you don't want it on your report)
10.) Done

Best, Z

How to force output to show only 1 row if multiple values appear

2 Answers2