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

On 22-Mar-2014 00:55 -0700, Birgitta Hauser wrote:

CRPence on Friday, 21.3.2014 21:51 wrote:

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). <<SNIP>>

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 parentheses.
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
)
;

And 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
;

I was actually aware of that, but I can not test anything beyond v5r3 :-( I should have clarified that the restriction I had noted, was known to exist for that earlier release, and I should have offered additionally, what I expected would be valid [though untested] in newer releases. Yet, there was no mention of the available VRM in the OP. So, that the code I offered operates within the limitations of older releases as well as on newer releases, makes my examples functional for a wider audience :-)

Unfortunately I chose to be curt and was being somewhat cynical in my reply, because I recalled offering up effectively the same reply as quoted just above, in the recent past topic "Subject: SQL UNION and ORDER BY" <http://archive.midrange.com/midrange-l/201402/msg00330.html#> by the same OP; FWiW, the above reply makes an even better conclusion to what is in that earlier thread. However I just checked, and noticed that my reply was never sent [found the message in my /drafts/ folder], either because my system crashed [e.g. per power loss, as I have no battery] or I had decided your reply <http://archive.midrange.com/midrange-l/201402/msg00346.html> was sufficient. But I see now, in review [rather than depending solely on my recollection], that discussion did not include the FETCH FIRST clause, just ORDER BY. Consider my smirking expression erased, replaced with that of shame.

FWiW: I expect the UNION shown in the example from the OP as one of the "variations" tried, does not express an explicit intent. The original UNION [DISTINCT] was replaced by a UNION ALL in my reply [though snipped], because the expressed requirements seem not to want to effect distinct processing on the results of each subquery. Thus there would seem no reason to effect de-duplication of those two sets via distinct processing on the overall result set; i.e. while fewer than "5 rows" may result naturally due to a lack of that many matching rows, fewer than five rows resulting instead due to distinct processing would seem to vitiate the expressed requirements. And if each of the combined sets are already naturally distinct, then any work to perform distinct processing on the overall result set, would be unnecessary overhead.