Removing duplicates from select query output

Question

Could anyone please suggest what is the error in the below query? Actually I want to remove all duplicates from the select query output and fetch only unique rows. Thanks in advance.

select *
from (
    select ROW_NUMBER() over (
            partition by Dep
            , tariffkode
            , LinkTariffType
            , poliata
            , poliatavia
            , podiata
            , podiatavia
            , PreCarriage
            , PortTerminalId
            , Product
            , RoutingOrder
            , PrepaidCollect
            , isnull(description, '')
            , ScaleCalcCode
            , isnull(scalefrom, 0)
            , isnull(scaleto, 0)
            , CurrencyCode
            , Base order by LinkTariffType desc
            ) Record
        , *
    from (
        select tn.LinkTariffType
            , tn.Dep
            , tn.POLIata
            , tn.POLIatavia
            , tn.PODIata
            , tn.PODIatavia
            , tn.CurrencyCode
            , tn.LegalEntityID
            , tn.Rate
            , tn.Base
            , tn.Minimum
            , tn.NrDescription
            , tn.Description
            , tn.DateFrom
            , tn.DateUntil
            , tn.DateCreate
            , tn.DateMod
            , tn.ModName
            , tn.Tariffkode
            , tn.ExpiryDate
            , tn.PClass
            , tn.Maximum
            , tn.RoutingOrder
            , tn.TariffCompType
            , tn.PrePaidCollect
            , tn.Product
            , tn.IsDeleted
            , (
                select distinct Location_IATA
                from Company
                where Called = 'KARL KING'
                    and LegalEntityID = 1
                    and IsDeleted = 0
                ) as PreCarriage
            , tn.PortTerminalID
            , tn.ScaleFrom
            , tn.ScaleTo
            , tn.ScaleCalcCode
            , tn.Mandatory
            , (
                select CompanyID
                from PlaceOfReceipt
                where warehouse = 'KARL KING'
                    and LegalEntityID = 1
                    and OfficeID = 13
                    and IsDeleted = 0
                ) as WarehouseID
            , tn.TariffRelID
            , tn.FreeDescription
            , 0
            , tn.ShipCode
            , tn.AgentID
            , tn.ContainerTypeID
            , tn.CommodityID
            , 0 as TempTable
        from TariffNew tn
        inner join hhInvoiceLines inv
            on tn.Tariffkode = inv.NrInvoiceLine
        where (
                tn.PreCarriage is not null
                and tn.PreCarriage != ''
                )
            and (
                tn.POLIata is not null
                and tn.POLIata != ''
                )
            and inv.OfficeID = 13
            and inv.IsDeleted = 0
            and inv.LegalEntityID = 1
            and tn.LegalEntityID = 1
            and tn.Dep = 'E'
            and tn.IsDeleted = 0
            and tn.DateUntil = '2078-12-31 00:00:00'
            and tn.Description = 'kgl'
        )
    ) as b
where b.Record = 1

I tried with distinct. but it's not working.

Che
– Che

2017-02-21 12:56:17 +00:00
Commented Feb 21, 2017 at 12:56 — Che
– Che, Commented Feb 21, 2017 at 12:56
Please make some effort to format the query.

Gordon Linoff
– Gordon Linoff

2017-02-21 12:56:42 +00:00
Commented Feb 21, 2017 at 12:56 — Gordon Linoff
– Gordon Linoff, Commented Feb 21, 2017 at 12:56

SqlZim · Accepted Answer · 2017-02-21 13:38:52Z

First, you have to define what you want to call "unique rows".

Once you have the set of columns that determines a row is unique, that is the set of columns you use in the partition by part of row_number()

In the code below, uncomment the column set that defines your "unique rows":

  select *

  from (
   select ROW_NUMBER() over (
     partition by 
       Dep
     --, tariffkode
     --, LinkTariffType
     --, poliata
     --, poliatavia
     --, podiata
     --, podiatavia
     --, PreCarriage
     --, PortTerminalId
     --, Product
     --, RoutingOrder
     --, PrepaidCollect
     --, isnull(description, '')
     --, ScaleCalcCode
     --, isnull(scalefrom, 0)
     --, isnull(scaleto, 0)
     --, CurrencyCode
     --, Base 
     order by LinkTariffType desc
     ) Record
    , *

   from (
    select tn.LinkTariffType
     , tn.Dep
     , tn.POLIata
     , tn.POLIatavia
     , tn.PODIata
     , tn.PODIatavia
     , tn.CurrencyCode
     , tn.LegalEntityID
     , tn.Rate
     , tn.Base
     , tn.Minimum
     , tn.NrDescription
     , tn.Description
     , tn.DateFrom
     , tn.DateUntil
     , tn.DateCreate
     , tn.DateMod
     , tn.ModName
     , tn.Tariffkode
     , tn.ExpiryDate
     , tn.PClass
     , tn.Maximum
     , tn.RoutingOrder
     , tn.TariffCompType
     , tn.PrePaidCollect
     , tn.Product
     , tn.IsDeleted
     , (
      select distinct Location_IATA
      from Company
      where Called = 'KARL KING'
       and LegalEntityID = 1
       and IsDeleted = 0
      ) as PreCarriage
     , tn.PortTerminalID
     , tn.ScaleFrom
     , tn.ScaleTo
     , tn.ScaleCalcCode
     , tn.Mandatory
     , (
      select CompanyID
      from PlaceOfReceipt
      where warehouse = 'KARL KING'
       and LegalEntityID = 1
       and OfficeID = 13
       and IsDeleted = 0
      ) as WarehouseID
     , tn.TariffRelID
     , tn.FreeDescription
     , 0 as UnnamedColumn
     , tn.ShipCode
     , tn.AgentID
     , tn.ContainerTypeID
     , tn.CommodityID
     , 0 as TempTable
    from TariffNew tn
        inner join hhInvoiceLines inv on tn.Tariffkode = inv.NrInvoiceLine
    where tn.PreCarriage is not null
      and tn.PreCarriage != ''
      and tn.POLIata is not null
      and tn.POLIata != ''
      and inv.OfficeID = 13
      and inv.IsDeleted = 0
      and inv.LegalEntityID = 1
      and tn.LegalEntityID = 1
      and tn.Dep = 'E'
      and tn.IsDeleted = 0
      and tn.DateUntil = '2078-12-31 00:00:00'
      and tn.Description = 'kgl'
    ) as s
   ) as b
  where b.Record = 1

thanks for the reply. still getting the same error at the line where the statement " ) as b where b.Record = 1) " is written. the error says that "Incorrect syntax near ). Expecting AS, ID or QUOTED_ID." Meanwhile I have found an another way to solve this issue. I have used CTE to store all the rows from the inner select query and then I have applied the partition on the contents of the CTE.

Collectives™ on Stack Overflow

Removing duplicates from select query output

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related