Group-wise maximum

#11

Captain Paralytic schrieb:

Quote:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
Why is there no more direct way to do this? Something
like "Scan table; if new article: add to result; else if new price
higher: update result; else ignore entry."
Whilst, dependent on the data, this may not always be the fastest
method, I do not see that it requires any more combinations than your
proposal.

Hm. Suppose I have an article A in my database which has five entries
with the prices 1, 2, 3, 4 and 5 <whatever-currency> respectively. The
strawberry would need to look at the combinations

A 1 A 2
A 1 A 3
A 1 A 4
A 1 A 5
A 2 A 3
A 2 A 4
A 2 A 5
A 3 A 4
A 3 A 5
A 4 A 5
A 5 NULL -> found

A "simple loop" would look at:
A 1
A 2
A 3
A 4
A 5 -> found

That's 11 and O(n^2) vs. 5 and O(n). And that doesn't even include that
all the combinations *not* matching the ON clause would have to be
iterated over as well. Unless I misunderstood something fundamental,
"JOIN" basically means "inner loop" and thus
O(n^<number-of-joined-tables>). But please correct me if I'm wrong, I
admit I'm not an expert in SQL matters.

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

#12

"Erick T. Barkhuis" <erick.use-net (AT) ardane (DOT) c.o.m> wrote

Quote:

Christopher M.:

I'm an old-school SQL guy, just getting into MySQL now.

Well, welcome to MySQL!

Any tips on selecting group-wise maximums?
http://dev.mysql.com/doc/refman/5.1/...group-row.html

Should I avoid subqueries, and nested joins, and instead use a cursor?

No, you shouldn't. There's no such rule as "avoid A, use B" as long as
you don't look at the parameters of your specific case.

Why don't you simply fill a test set-up with data, and try both
alternatives? See what is faster, AND have a look at what you're
comfortable with, when you're the one who is going to keep working on
the application, even after several years.
After all, even the fastest query is worthless, if you're not able to
perform maintenance in the future.

I tried joining to a nested SELECT statement, because it was fastest.

I'm also using the limit statement.

I'm getting inconsistent results.

W. Pooh (AKA Winnie P.)

#13

"Christopher M." <nospam_flibbity (AT) floo (DOT) com> wrote

Quote:

"Erick T. Barkhuis" <erick.use-net (AT) ardane (DOT) c.o.m> wrote in message
news:9qfat4FmvmU1 (AT) mid (DOT) individual.net...
Christopher M.:

I'm an old-school SQL guy, just getting into MySQL now.

Well, welcome to MySQL!

Any tips on selecting group-wise maximums?
http://dev.mysql.com/doc/refman/5.1/...group-row.html

Should I avoid subqueries, and nested joins, and instead use a cursor?

No, you shouldn't. There's no such rule as "avoid A, use B" as long as
you don't look at the parameters of your specific case.

Why don't you simply fill a test set-up with data, and try both
alternatives? See what is faster, AND have a look at what you're
comfortable with, when you're the one who is going to keep working on
the application, even after several years.
After all, even the fastest query is worthless, if you're not able to
perform maintenance in the future.

I tried joining to a nested SELECT statement, because it was fastest.

I'm also using the limit statement.

I'm getting inconsistent results.

W. Pooh (AKA Winnie P.)

Nevermind. I was just sorting by the wrong table.

W. Pooh (AKA Winnie P.)

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

Group-wise maximum

comp.databases.mysql comp.databases.mysql

Re: Group-wise maximum - 02-21-2012 , 08:04 AM

Re: Group-wise maximum - 02-27-2012 , 10:37 PM

Re: Group-wise maximum - 02-27-2012 , 10:52 PM