select first and union

dtrobert · #1 (**permalink**) 09-23-10, 15:22

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.

InformixWilli · #2 (**permalink**) 09-24-10, 05:33

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.

dtrobert · #3 (**permalink**) 09-24-10, 12:32

Well, I cannot write into this database and need a single query to fetch the data, ideally.

InformixWilli · #4 (**permalink**) 09-24-10, 15:18

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech