Selecting all rows after a row with specific values without repeating the same subquery

I have a table t1 and t2 which I join and order to form data set set1.

Two columns c1 and c2 form a unique identifier for the rows in set1.

I want to get all values from set1 after the first row with a specific c1 and c2.

I have a query like the one below which works, but it rep开发者_StackOverflow中文版eats the same subquery twice, which seems superfluous and overly complex even for Oracle:

SELECT * FROM
(
  SELECT row_number() OVER (ORDER BY c1, c3) myOrder, c1, c2, c3
  FROM t1, t2
  WHERE condition
  ORDER BY conditions
) sub1,
(
  SELECT sub1_again.myOrder FROM
  (
    SELECT row_number() OVER (ORDER BY c1, c3) myOrder, c2, c3
    FROM t1, t2
    WHERE condition
    ORDER BY conditions
  ) sub1_again
  WHERE sub1_again.c2 = "foo" AND sub1_again.c3 = "bar"
) sub2
WHERE sub1.myOrder >= sub2.myOrder
ORDER BY sub1.myOrder

It seems like SQL would have a simple way to do this, but I am not sure what to search for. Is there a cleaner way to do this?

---

SELECT * FROM (
  SELECT row_number() OVER (ORDER BY c1, c3) myOrder, c2, c3
        ,CASE WHEN c2 = "foo" AND c3 = "bar"
              THEN row_number() OVER (ORDER BY c1, c3)
         END target_rn
  FROM t1, t2
  WHERE condition
  ORDER BY conditions
) WHERE myOrder > target_rn;

---

I think there is something missing in the accepted solution. However, it helped me to come up with this:

SELECT * FROM (
  SELECT row_number() OVER (ORDER BY c1, c3) myOrder, c1, c2, c3,
    max(case when c2 = "foo" AND c3 = "bar" then 1 else 0 end) over (order by c1, c3) rowFound,
  FROM t1, t2
  WHERE condition
)
WHERE rowFound > 0
ORDER BY conditions

Basically the case is selecting which row is the one to start from, and the max "drags" the value from that row onwards. The last WHERE does the filtering.

---

Please try to be more specific,if i understood well you have just to add the parameter 'where',like:SELECT * FROM **where** element