Re: Getting the intended LIMIT, OFFSET results from a JOIN?

#1

On Feb 3, 6:26*pm, Puzzled <scratching.h... (AT) example (DOT) com> wrote:

Quote:

oops. I inadvertently re-used "n", probably making things
confusing. * I should have written:

I'm doing a JOIN on 2 tables, where the relationship is n:1 USING
( Foo ), and want a well-defined subset of the total (LIMIT w
OFFSET x ). * Since it's a JOIN, the LIMIT only returns w records
whereas I want it to return w distinct Foos regardless of how
many raw records the n:1 relationship requires.

Is there a slicker way to do it than the brute-force way of
getting DISTINCT Foo LIMIT w OFFSET x and then doing a second
JOIN WHERE Foo IN ( the Foos returned from the first JOIN )?

I've fooled around with it, but can't seem to find anything that
works. *Yet it's such a common thing to do that I have to suppose
there's a better solution than brute-force.

TIA for any thoughts.- Hide quoted text -

- Show quoted text -

My thought is that without an order by clause how can you guarantee
that what you get is what you wanted? MYSQL is not a spreadsheet where
you can get row 1 and row x and row y as the result set order is
indeterminate until AFTER the order by clause. Be very careful you
know exactly what you are getting.

case and point. Given tablex has 1M rows

select * from tablex limit 10 will:
1) do a FULL TABLE SCAN
2) give you the first 10 **RANDOM** rows. They are RANDOM because
they have not been determined by the order by clause. You may get
lucky and get what you were looking for, but you cannot guarntee this
to be the case.

#2

On 2011-02-05 16:18, Puzzled wrote:
[...]

Quote:

But so far, although I can describe what I want in English, I
can't seem to map that onto SQL. Whatever I do, I end up with
multiple copies of the base record each glued to one of the
fanout records whereas what I need is all the fanout records for
one base record glued to one copy of that base record, or a way
to determine how many of those 1:1 intermediate records will make
up that one finished record so that I can trick the limit. What's
another pain is that I have 3 heavyweight db books including Date
and none of them covers this situation.

Can you post create table statements, insert statements with sample
data, and the expected output?

/Lennart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Getting the intended LIMIT, OFFSET results from a JOIN?

comp.databases.mysql comp.databases.mysql

Re: Getting the intended LIMIT, OFFSET results from a JOIN? - 02-04-2011 , 02:45 PM

Re: Getting the intended LIMIT, OFFSET results from a JOIN? - 02-05-2011 , 09:33 AM