CODEWITHSUNDEEP
mysqlsql
Sandeep Nair
Sandeep Nair

Reputation: 456

Mysql Select rows based on whether other column is null

I have a legacty table "wages" that I cannot change and I want to get results from this table such that i get allowedWage for a country based on whether there is a value for farmer or not otherwise get the allowedWage for worker

Input

id    country farmer worker allowedWage
1     US      1      null   100
2     US      null   1      50
3     AU      1      null   60
4     CA      null   1      80

Expected Output

  id country allowedWage
   1  US      100
   3  AU      60
   4  CA      80

so I wrote the following query if someone wants to find the wage for country US, AU, CA and IN

    select id, country, allowedWage from wages 
where country in ('US', 'AU', 'CA', 'IN') 
and ((farmer = 1 and worker is null) or (worker = 1 or farmer is null))

but this obviously gives all row and I am trying to figure out if there is a way to exclude worker data for a country if there is value of farmer

Actual Output

  id country allowedWage
   1  US      100
   2  US      50 
   3  AU      60
   4  CA      80

Upvotes: 1

Views: 101

Answers (3)

forpas
forpas

Reputation: 164064

You can do it with conditional aggregation:

SELECT country,
       COALESCE(MAX(CASE WHEN farmer THEN allowedWage END), MAX(allowedWage)) allowedWage
FROM wages 
GROUP BY COUNTRY

For MySql 8.0+ you can do it with FIRST_VALUE() window function:

SELECT DISTINCT country,
       FIRST_VALUE(allowedWage) OVER (PARTITION BY country ORDER BY farmer = 1 DESC) allowedWage
FROM wages

See the demo.
Results:

> country | allowedWage
> :------ | ----------:
> AU      |          60
> CA      |          80
> US      |         100

Upvotes: 1

GMB
GMB

Reputation: 222382

You could use row_number(), if you are running MySQL 8.0.

select *
from (
    select t.*,
        row_number() over(partition by country order by (farmer <=> 1) desc) rn
    from mytable t
)
where rn = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Hmmm . . . this a prioritization query. You can use not exists to select the workers where necessary:

select id, country, allowedWage
from t
where farmer = 1 or
      not exists (select 1
                  from t t2
                  where t2.country = t.country and t2.farmer = 1
                 );

Note that the worker column doesn't seem necessary because all the information needed is in the farmer column.

Upvotes: 0

Related Questions