select * from file1
where field1 = 'A'
FETCH FIRST 5 ROWS ONLY
union
select * from file1
where field1 = 'B'
FETCH FIRST 5 ROWS ONLY
To use the FETCH ... ONLY clause in that context, the subquery must be a
derived table expression; e.g. a Nested Table Expression (NTE).
This rule was removed with release 6.1, i.e. FETCH FIRST x ROWS and ORDER BY
can be directly used within a Sub-Select. But the Sub-Select must be
enclosed in parenthesis.
The following statement works on Release 7.1 without any problem:
(Select * from MyTable
Where MyField1 = 'A'
Order By MyField2 Desc
Fetch First 5 rows only)
Union
(Select * From MyTable
Where MyField1 = 'B'
Order By MyField2 Desc
Fetch First 5 Rows Only);
An even the following statement works without any problems:
(Select * from MyTable
Where MyField1 = 'A'
Order By MyField2 Desc
Fetch First 5 rows only)
Union
(Select * From MyTable
Where MyField1 = 'B'
Order By MyField2 Desc
Fetch First 5 Rows Only)
Order By MyField2, MyField1;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Friday, 21.3 2014 21:51
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: SQL: how to select 5 rows of this and 5 rows of that
On 21-Mar-2014 12:41 -0700, Stone, Joel wrote:
I would like to select 5 rows (of thousands) where field1 = 'A' and
union that with 5 rows where field1 = 'B'
How can I accomplish this with sql?
I have tried variations of
select * from file1
where field1 = 'A'
FETCH FIRST 5 ROWS ONLY
union
select * from file1
where field1 = 'B'
FETCH FIRST 5 ROWS ONLY
To use the FETCH ... ONLY clause in that context, the subquery must be a
derived table expression; e.g. a Nested Table Expression (NTE):
select *
from ( select * from file1 where field1 = 'A'
FETCH FIRST 5 ROWS ONLY ) as Arows
union all
select *
from ( select * from file1 where field1 = 'B'
FETCH FIRST 5 ROWS ONLY ) as Brows
Or, using a Common Table Expression (CTE):
with
Arows as ( select * from file1 where field1 = 'A'
FETCH FIRST 5 ROWS ONLY )
, Brows as ( select * from file1 where field1 = 'B'
FETCH FIRST 5 ROWS ONLY )
select * from Arows
union all
select * from Brows
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
