DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Question query matching mode

    Hello guies,
    I have a simple table such as
    id code
    1 201
    1 202
    1 203
    2 201
    2 202

    I want to write a query to get the IDs based on codes. For example, I want to get the IDs if they have code 201 and 202. I can do it with the UNION function and id 1 and 2 will be returned.
    But how can I find the IDs who has only codes: 201 and 202? That is, I want to get id 2 not 1 and 2.

    Please advise me. Thanks,


  2. #2
    Join Date
    Feb 2004
    Try this.

    select id
    from ( select id, code,
    lag(code,1) over (partition by id order by code) prev_code1,
    lead(code,1) over (partition by id order by code) next_code1,
    lead(code,2) over (partition by id order by code) next_code2
    from your_table)
    where prev_code1 is null
    and next_code2 is null
    and next_code1 = 202
    and code = 201

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.