Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    40

    Unanswered: select first and union

    Hi,
    I was wondering if anyone found a good workaround to collecting a limited number of rows from a union-style query. I basically have something like


    select first 500 col1, col2, col3...
    union
    select first 500 col1, col2, col3...
    order by col1

    and get the result

    Cannot use 'first', 'limit' or 'skip' in this context.

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Its not possible to use First in the second statement, but you can use it in the first one:
    select first 500 col1, col2, col3...
    union
    select first 500 col1, col2, col3...
    order by col1

    You can solve the problem with a temp table:
    Code:
    select first 5 customer_num from customer order by customer_num desc
    into temp tmp1;
    
    insert into tmp1 select first 5 customer_num from customer;
    
    select * from tmp1 order by customer_num;
    You get the first and last 5 customer_num's.

  3. #3
    Join Date
    Feb 2010
    Posts
    40
    Well, I cannot write into this database and need a single query to fetch the data, ideally.

  4. #4
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Its possible with a little bit crazy shit from IDS 11.50

    Code:
    select    *
    from      (select first 500 col1, col2, col3,... from tab1)
    union
    select    *
    from      (select first 500 col1, col2, col3,... from tab2)
    order by  1;
    Works with my xC6.

Posting Permissions

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