How to avoid duplicates of a single ID in SQL; Select Distinct-CodePudding

I have some SQL code that reads like this. It intends to grab all of the data meeting the two conditions, but not to grab the data if we already have a row with the same ID as it. Select Distinct (t1.ID) works as intended, but when I add in the additional variables, it no longer filters properly.

Select Distinct (t1.ID),t1.Var2, t1.Var3...
FROM table_location AS t1
WHERE t1.FCT_BILL_CURRENCY_CODE_LCL = 'USD'
  AND t1.RQ_GLOBAL_REGION = 'North America'

enter image description here

This clearly contains multiple rows with the same ID, contrary to how it should work. How do I fix this?

CodePudding user response：

I'm not sure what DB you're using, but most will have the concept of numbering rows by a partition.

To get a distinct by a certain value, you need to make a subquery that selects your data plus a row number that is partitioned by your distinct property, then have your parent query select only the rows with 1 as the row number to get just the first of each.

CodePudding user response：

I have added a query by looking into the sample query you mentioned in the problem. If you add sample data, the we will have better understanding of the problem.

Query

Select t1.ID,t1.Var2, t1.Var3...
From(
        Select t1.ID,t1.Var2, t1.Var3...,
                row_number()over(partiton by t1.ID order by t1.ID) as Rnk_ID
        FROM table_location AS t1 
        WHERE t1.FCT_BILL_CURRENCY_CODE_LCL = 'USD' AND t1.RQ_GLOBAL_REGION = 'North America'
    )qry1
    WHERE Rnk_ID = 1