1

Topic: Help with request

Good afternoon.
There is a table with id and its value.
It is necessary to write request which on everyone id which matters 0 and 1 deduced only value 0.
create table #z (id int, qua int)
Insert #z
select 1, 0 union all
select 1, 1 union all
select 2, 0 union all
select 3, 1 union all
select 4, 1 union all
select 4, 0 union all
select 5, 1
I.e. it should turn out:
1 - 0
2 - 0
3 - 1
4 - 0
5 - 1

2

Re: Help with request

Asic, and in what a problem that?

Select id, qua
from #z a
Where qua = 1 and not exists (Select 1 from #z b where a.id = b.id and b.qua = 0)
Union
Select id, qua
from #z a
Where qua = 0

Or it is necessary by means of modern frame functions?
Them is at me:

Select top (1) with ties id, qua
from #z
order by ROW_NUMBER () over (partition by id order by qua ASC) ASC

3

Re: Help with request

Group on int and take min (qua)

4

Re: Help with request

Group on ID and take min (qua)

5

Re: Help with request

Explain

Asic wrote:

Good afternoon.
It is necessary to write request which on everyone id which matters 0 and 1 deduced only value 0 .
I.e. it should turn out:
1 - 0
2 - 0
3 - 1
4 - 0
5 - 1

6

Re: Help with request

KRS544 wrote:

Explain
it is passed...

For each value id it is required minimum qua among all lines with it id.
It if in a simple way

7

Re: Help with request

iap wrote:

it is passed...
For each value id it is required minimum qua among all lines with it id.
It if in a simple way

If qua> =0, certainly

8

Re: Help with request

select id, min (qua)
from #z
group by id