CODEWITHSUNDEEP
sqlsql-server
F. Baum
F. Baum

Reputation: 300

SQL: Specific distinct columns out of 3 columns

We have a table with 2 columns:

ZahlBed | Bez
------------------------------------------------------
60      | 60 Tage betto. Für Rechnungen & Gutschriften
60      | 60 Tage netto. Für Rechnungen & Gutschriften
60      | ne
14      | 14 Tage netto. Für Rechnungen & Gutschriften
.
.
.

Now we want to search for a given string (i.e. 60) in both columns and weight the result with a SortFactor:

The first Statement will search for '60' in 'ZahlBed' and has the sortfactor 10. The second searches for '%60%' and will give us 600 or so and has the sortfactor 11. The third searches for '%60%' in the 'Bez'-column, sortfactor 20. The last searches for 60 in the 'Bez'-column, sortfactor 15. At the end, it will order it by the SortFactor.

SELECT DISTINCT TOP 20 ZahlBed,Bez,SortFactor FROM ((
SELECT ZahlBed,Bez ,10 AS SortFactor FROM XXAV_CMCTermOfPayment WHERE  (ZahlBed LIKE 60) ) UNION  
SELECT ZahlBed,Bez ,11 AS SortFactor FROM XXAV_CMCTermOfPayment WHERE ZahlBed  LIKE  '%60%' AND LEN(ZahlBed) <> LEN('60')  UNION  
SELECT ZahlBed,Bez ,20 AS SortFactor FROM XXAV_CMCTermOfPayment WHERE Bez  LIKE  '%60%'   UNION  
SELECT ZahlBed,Bez ,15 AS SortFactor FROM XXAV_CMCTermOfPayment WHERE Bez  LIKE  60)  A  
ORDER BY SortFactor ASC

The expected Result is this:

ZahlBed | Bez                                          | SortFactor
-------------------------------------------------------------------
60      | 60 Tage betto. Für Rechnungen & Gutschriften | ...
60      | 60 Tage netto. Für Rechnungen & Gutschriften | ...
60      | ne                                           | ...

Instead we are getting this:

ZahlBed | Bez                                          | SortFactor
--------------------------------------------------------------------
60      | 60 Tage betto. Für Rechnungen & Gutschriften | 10
60      | 60 Tage netto. Für Rechnungen & Gutschriften | 10
60      | ne                                           | 10
60      | 60 Tage betto. Für Rechnungen & Gutschriften | 20
60      | 60 Tage netto. Für Rechnungen & Gutschriften | 20

Our Problem now is, that we are getting duplicates in the columns 'ZahlBed' and 'Bez' but not in 'SortFactor'.

How can we only get distinct values?

Upvotes: 1

Views: 75

Answers (2)

SqlZim
SqlZim

Reputation: 38063

You could use a case expression to calculate your SortFactor once for each row instead:

select top 20 
    ZahlBed
  , Bez
  , SortFactor
from (
  select 
      ZahlBed
    , Bez 
    , case 
        when ZahlBed like 60 then 10
        when ZahlBed  like '%60%' and len(ZahlBed) <> len('60') then 11
        when Bez like '%60%' then 15
        when Bez like 60 then 20
        else -1
        end as SortFactor
  from xxav_cmctermOfPayment
  ) s
where SortFactor > 0
order by SortFactor

Alternately, you could aggregate your results with group by and take the min(SortFactor):

select top 20 
    ZahlBed
  , Bez
  , min(SortFactor) as SortFactor
from (select ZahlBed, Bez, 10 as SortFactor from xxav_cmctermOfPayment where (ZahlBed like 60) 
union select ZahlBed, Bez, 11 as SortFactor from xxav_cmctermOfPayment where ZahlBed  like '%60%' and len(ZahlBed) <> len('60')
union select ZahlBed, Bez, 20 as SortFactor from xxav_cmctermOfPayment where Bez  like '%60%'
union select ZahlBed, Bez, 15 as SortFactor from xxav_cmctermOfPayment where Bez  like 60
)  A  
group by ZahlBed, Bez
order by min(SortFactor) asc

rextester demo (SQL Server): http://rextester.com/WPIDS94742

rextester demo (Oracle): http://rextester.com/RUDA52769

Upvotes: 2

Utsav
Utsav

Reputation: 8103

Try running this.

select top 20 ZahlBed, Bez, SortFactor from
    (
    select ZahlBed,Bez, 
        case 
        when (ZahlBed LIKE 60) then 10
        when ZahlBed  LIKE  '%60%' AND LEN(ZahlBed) <> LEN('60') then 11
        when Bez  LIKE  '%60%' then 20
        when Bez  LIKE  60 then 15 
        end
    as sortFactor
    from XXAV_CMCTermOfPayment
    where 
        (
            ZahlBed LIKE 60
            or
            (ZahlBed  LIKE  '%60%' AND LEN(ZahlBed) <> LEN('60') )
            or
            Bez  LIKE  '%60%'
            or
            Bez  LIKE  60
        )
    ) t 
order by SortFactor

Upvotes: 1

Related Questions