query matching mode
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: query matching mode

  1. #1
    Join Date
    Jan 2004
    Posts
    9

    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,

    Jerry

  2. #2
    Join Date
    Feb 2004
    Posts
    77
    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