Re: LIMIT within UNION?

On Thu, 12 Sep 2002, Stephan Szabo wrote:

> On Thu, 12 Sep 2002, Andrew Perrin wrote:
> 
> > Greetings-
> >
> > I have a table of participants to be contacted for a study. Some are in
> > the "exposure" group, others in the "control" group. This is designated by
> > a column, typenr, that contains 1 for exposure, 2 for control.
> >
> > The complication is this: I need to select 200 total. The 200 number
> > should include *all* those eligible in the exposure group, plus enough
> > from the control group to bring the total number up to 200. (Yes, there is
> > a valid reason for this.) Furthermore, I need to sort the output of the
> > two groups *together* by zip code.
> 
> Do you get more than 200 if there are more eligible people 

Yes - in the (rather rare) case that there are 200 or more eligible
exposure subjects, the result set should be the total number of eligible
exposure subjects.

> and does the
> ... ever include the same person in both sides of the union?

No; each person is only in one of the two sides.

> 
> If not in the second case, union all would probably save the database
> some extra work since it won't have to try to weed out duplicates.

I'll try that.

> 
> If not in the first case, then wouldn't a limit 200 on the after union
> result set work rather than a separate count and subtraction?
> 

Interesting - this would count on the UNION including all cases in the
first query before those in the second query. Are UNIONed records
presented in any predictable order?



----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu