CODEWITHSUNDEEP
sqlsql-servert-sql
Luis Orihuela
Luis Orihuela

Reputation: 11

Take precedence on a specific value from a table

For each person's distinct record that has a toyota, only take toyota and filter out that person's other cars else bring all cars.

This is what I'm looking for.

The actual script will not match my logic above. I was trying to simplify my question by using random names and car brands, but the objective was the same since I wanted to get a specific address code and filter out the rest if it did exist for other vendor names (see below). Thank you, GMB.

GPMEM.dbo.PM00200 a           -- Vendor Master
LEFT JOIN GPMEM.dbo.PM30200 b -- Historical/Paid Transactions
ON a.VENDORID = b.VENDORID
LEFT JOIN GPMEM.dbo.PM20000 c -- Open/Posted Transactions
ON a.VENDORID = c.VENDORID
LEFT JOIN (
SELECT d.*,
    rank() over(
            partition by d.VENDORID
            order by case when d.ADRSCODE = 'ACH' THEN 0 ELSE 1 END
             )rn
FROM GPMEM.dbo.PM00300 d
) d  -- Vendor Address Master
ON a.VENDORID = d.VENDORID
WHERE
d.rn = 1

Upvotes: 0

Views: 342

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this with filtering logic:

select t.*
from t
where t.colb = 'toyota' or
      not exists (select 1 from t t2 where t2.cola = t.cola and t2.colb = 'toyota');

If I were to use window functions for this, I would simply count the toyotas:

select t.*
from (select t.*,
             sum(case when colb = 'toyota' then 1 else 0 end) over (partition by cola) as num_toyotas
      from t
     ) t
where colb = 'toyota' or num_toyotas = 0;

Upvotes: 0

GMB
GMB

Reputation: 222432

You can use window functions:

select colA, colB
from (
    select 
        t.*, 
        rank() over(
            partition by colA 
            order by case when colB = 'Toyota' then 0 else 1 end
        ) rn
    from mytable t
) t
where rn = 1

The trick likes in the order by clause in the over() clause of window function rank(): if a person has a Toyota, it will be ranked first, and their (possible) other cars will be ranked second. If it has no Toyota, all their car will be ranked first.

Upvotes: 1

Related Questions