Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    1

    Unanswered: hepl required in selecting rows between 20 to 30

    i want to select records between 20 to 30 on a condition
    i tried with rowid but its fixed for a row. but the rowid is
    generated like sequence no.
    So the following query is not working

    select PERSON_ID, rowid as LINENUM from toon_person as A
    where A.IS_DRINKER = 'N' and A.IS_SMOKER = 'N' and rowid between 20 and 30

    So i used the first n row.
    The query is as follows

    select first 30 A.PERSON_ID from TOON_PERSON A where A.PERSON_ID NOT
    IN( select first 20 A.PERSON_ID from TOON_PERSON A where
    A.IS_DRINKER = 'N' and A.IS_SMOKER = 'N' ) and A.IS_DRINKER = 'N'
    and A.IS_SMOKER = 'N'

    but first is not supported in subquery.
    Please help me out what is the alternate way

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    Not pretty, but I think it should work...


    select first 20 A.PERSON_ID from TOON_PERSON A where
    A.IS_DRINKER = 'N' and A.IS_SMOKER = 'N' ) and A.IS_DRINKER = 'N'
    and A.IS_SMOKER = 'N' into temp first20 with no log;

    select first 30 A.PERSON_ID from TOON_PERSON A where
    A.IS_DRINKER = 'N' and A.IS_SMOKER = 'N' ) and A.IS_DRINKER = 'N'
    and A.IS_SMOKER = 'N' into temp first30 with no log;

    select A.PERSON_ID
    from first30 A
    where A.PERSON_ID NOT IN ( Select b.person_id from first20 b)
    rws

Posting Permissions

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