MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

AW: SQL: how to select 5 rows of this and 5 rows of that



fixed

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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact