CODEWITHSUNDEEP
sqljoin
Sébastien Pasquali
Sébastien Pasquali

Reputation: 3

self join sql query with different conditions

I have a simple kpi table with id, name, value per below

id    name    value
-------------------
1     kpi1      100
1     kpi1      100
1     kpi1      100
1     kpi1      100
1     kpi2      100
1     kpi2      0
1     kpi2      100
1     kpi2      100
2     kpi1      100
2     kpi1      0
2     kpi1      0
2     kpi1      0
2     kpi2      100
2     kpi2      100
2     kpi2      100
2     kpi2      100

I want the query to return a distinct 1 line per id and per name for the value = 0 first if exists otherwise the value = 100.

id    name    value
-------------------
1     kpi1      100
1     kpi2      0
2     kpi1      0
2     kpi2      100

I tried using joins without really knowing if it is the right way because I do not how to add the condition to select a kpi with a value at 0 first and if it does exist a value at 100.

select * from (select * from kpi where value='0') a inner join (select * from kpi where value='100') b on a.id=b.id

Hope I am clear enough I spent the day figuring out joins without success. Thanks

Upvotes: 0

Views: 36

Answers (2)

Vad1m
Vad1m

Reputation: 441

SELECT id, name, min(value) as value 
from kpi
group by id, name

Upvotes: 1

zealous
zealous

Reputation: 7503

You can use window function. here is the demo.

  select
    id,
    name,
    value
from
(
    select
        id,
        name,
        value,
        row_number() over (partition by id, name order by id, value) as rnk
    from yourTable
) val
where rnk = 1

Output:

| id  | name | value |
| --- | ---- | ----- |
| 1   | kpi1 | 100   |
| 1   | kpi2 | 0     |
| 2   | kpi1 | 0     |
| 2   | kpi2 | 100   |

Upvotes: 0

Related Questions