Pull all records based on 2 criteria on separate field

[JoeZim]

My apologies if the title does not make sense, but I'm having a difficult time even trying to summarize my question. Here it is in a nutshell:

I have the following table:

Item pos
8529 2
8529 2
161637 2
161637 3
357346 1
357346 2
357346 2
367390 1
367390 1
393538 3
393538 3
393538 3
393538 2
393538 3
409654 1
409654 3

I'm trying to get down to the following result:

Item pos
357346 1
357346 2
357346 2
409654 1
409654 3


The criteria: Pull any item (and all records associated with that particular item #) if the item # has both:

a. pos = 1
b. pos > 1

So for instance, item # 8529 would not fit because the pos for this is always > 1. Item # 367390 would not fit because the pos is always = 1.

I have been mulling this over for a few hours now and can't seem to find anything that works. I've tried group by and using max(pos) <> 1 AND min(pos) = 1, but I don't think this is the way to go because I don't want to write an entire subquery. This seemed so easy at the beginning, but it is not turning out that way, for me at least.

Thanks, Joe

 

[markros]

select * from Items I where Pos = 1 and exists
(select 1 from Items where Items.Item = I.Item and Items.Pos > 1)

I forgot how this type of query is called correctly...

PluralSight Learning Library