By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,501 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,501 IT Pros & Developers. It's quick & easy.

Trying to SELECT a SELF JOIN Max value

P: n/a
Hello All, Hope some Guru will be able to help me with this.

Let's take this example table

A
-------------------------------------------------------------------------------
id | TicketNo | evaluation | Username
-------------------------------------------------------------------------------
1 1 9 Jamie
2 1 8.5 Jocasta
3 1 9.1 Claire
4 2 9 Jamie
5 2 7.3 Fergus
6 3 6 Fergus
-------------------------------------------------------------------------------
Basically, what I want to do is

FIND all the TicketNo for which the Maximum evaluation have been given
by Jamie.

It should then return :

4 2 9 Jamie

and my TicketNo would be 2.
If I want the same query for Fergus, the result would be:
5 2 7.3 Fergus
6 3 6 Fergus

And if I want for Jocasta there will be an empty set.

I have try so many different request, but I assume my Query should be
something like

SELECT id,TicketNo,evaluation,Username FROM A WHERE ....???

I suppose from here I should do a self JOIN on the table, joining the
TicketNo to the TicketNo, and finding the id with the MAXIMUM
"joinned" value of evaluation...

I'm getting headache!!

Please help!

Much thanks in advance!


Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Denis St-Michel" <ds*******@aei.ca> wrote in message
A
-------------------------------------------------------------------------- ----- id | TicketNo | evaluation | Username
-------------------------------------------------------------------------- ----- 1 1 9 Jamie
2 1 8.5 Jocasta
3 1 9.1 Claire
4 2 9 Jamie
5 2 7.3 Fergus
6 3 6 Fergus
-------------------------------------------------------------------------- -----

Basically, what I want to do is

FIND all the TicketNo for which the Maximum evaluation have been given
by Jamie.
Let's work towards a solution.

Which version of MySql are you using? Version 4.1 supports nested
sub-queries, meaning that in the where clause of a select statement you have
another select statement. I got a hunch those will come in handy here.

In earlier versions of MySql you can create a temporary table, do the
sub-query and store the results in this temporary table, then do the real
query which will join to this temporary table, then finally drop the
temporary table.
It should then return :

4 2 9 Jamie

and my TicketNo would be 2.
If I want the same query for Fergus, the result would be:
5 2 7.3 Fergus
6 3 6 Fergus
I don't understand why Fergus gave the largest evaluation for ticket 2.
Didn't Jamie give the highest evaluation, namely 9 points?

Let's clarify the algorithm you want, then we can come up with a SQL
statement.
And if I want for Jocasta there will be an empty set.

I have try so many different request, but I assume my Query should be
something like

SELECT id,TicketNo,evaluation,Username FROM A WHERE ....???

I suppose from here I should do a self JOIN on the table, joining the
TicketNo to the TicketNo, and finding the id with the MAXIMUM
"joinned" value of evaluation...


Something like

SELECT id,TicketNo,evaluation,Username FROM A WHERE EXISTS (SELECT ... FROM
A WHERE ...)

might work.

See my post with subject "find all products with maximum version" which you
can search in groups.google.com with

find all products with maximum version group:mailing.database.mysql

or if you can get this link to work:

http://groups.google.com/groups?hl=e...ic.15911%24_o3
..514673%40bgtnsc05-news.ops.worldnet.att.net&rnum=1&prev=/groups%3Fq%3Dfind%
2Ball%2Bproducts%2Bwith%2Bmaximum%2Bversion%2Bgrou p:mailing.database.mysql%2
6hl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dmailing.database.mysql%26selm%3DXDli
c.15911%2524_o3.514673%2540bgtnsc05-news.ops.worldnet.att.net%26rnum%3D1
Jul 20 '05 #2

P: n/a
On Fri, 27 Aug 2004 04:29:50 GMT, "Siemel Naran"
<Si*********@REMOVE.att.net> wrote:

Which version of MySql are you using? Version 4.1 supports nested
sub-queries, meaning that in the where clause of a select statement you have
another select statement. I got a hunch those will come in handy here.
I'ts 4.0.20-standard therefore it doesn't support nested
sub-queries...that's too bad..

I don't understand why Fergus gave the largest evaluation for ticket 2.
Didn't Jamie give the highest evaluation, namely 9 points?


You're totally right.. my mistake..
I finally been able to do what I Wanted by building my request this
way:

SELECT t1.id,t1.TicketNo,t1.evaluation,t1.Username FROM A t1 INNER
JOIN A t2 ON t1.TicketNo = t2.TicketNo GROUP BY t1.id, t1.TicketNo,
t1.evaluation, t1.Username HAVING t1.evaluation = MAX(t2.evaluation);

and it seems to be working well.
But I'm still concern about the efficiency. Is this the best way to do
it (considering I can't update to MySQL 4.1 for now) ..?

Thanks again,
Jul 20 '05 #3

P: n/a
"Denis St-Michel" <ds*******@aei.ca> wrote in message
I finally been able to do what I Wanted by building my request this
way:

SELECT t1.id,t1.TicketNo,t1.evaluation,t1.Username FROM A t1 INNER
JOIN A t2 ON t1.TicketNo = t2.TicketNo GROUP BY t1.id, t1.TicketNo,
t1.evaluation, t1.Username HAVING t1.evaluation = MAX(t2.evaluation);

and it seems to be working well.


Clever! Only thing is you have to repeat the select columns in the group
by, which means lots of typing if you have more columns.
But you mentioned you want to find ticket numbers where a certain Username
gave the maximum evaluation. For this I think the following should work:

SELECT t1.id,t1.TicketNo,t1.evaluation,t1.Username
FROM
A t1
INNER JOIN A t2 ON t1.TicketNo = t2.TicketNo
where t1.Username = 'Fergus'
GROUP BY t1.id, t1.TicketNo, t1.evaluation, t1.Username
HAVING t1.evaluation = MAX(t2.evaluation)
;

It's important to know what you're after, as you'd create different indexes
for each scenario: find ticket nos for which Username gave the maximum
evaluation as you said in the original post, or find the Username for every
ticket no who gave the maximum evaluation. They differ by just a where
clause on Username = ?. but may require different indices to optimize.
I think it should be efficient if you have an index on a ( TicketNo,
evaluation desc, Username ). Hopefully the engine drives by t2 on this
index, quickly finding the maximum evaluation for every ticket no, joins to
t1 on the same index using all three columns of the index. But running the
SQL in the control center shows it drives by t1 using no index, then joins
to t2 using the above index. Note: I may be reading the explain tab
incorrectly, but it lists t1 first then t2, so I think it's driving by t1.
And this even if I GROUP BY t1.TicketNo, t1.evaluation desc, t1.Username,
t1.id, or add an ORDER BY t1.TicketNo ..., or reverse the table list
(selecting from t2 first then t1).
Better yet, the engine realizes that the join to t1 selects the same row
(kind of) and so avoids the join, and instead just applies the where
Username = ? clause to determine whether to show this row or move on to the
next one.

Of course, in order to quickly find the maximum evaluation for each ticket
no, we'd like a special index: one way is that the index on ( TicketNo,
.... ) stores the TicketNo, followed by the number of records with this
TicketNo, followed by this many pointers to the original records. The
advantage of this data structure is that we can quickly find the maximum
evaluation for the first ticket no (it's the first record), then quickly
jump to the next ticket no (because we know the number of records to skip).
But I don't know what kind of indexes MySql uses, and create index does not
allow us to specify the type of index.
Note that the IN method (possible on version 4.1 and later) also drives by
no index on the outer table, then the index ( TicketNo, evaluation desc,
Username ) on t2. Again, this is assuming I'm reading the explain tab
correctly, which lists t1 first. Here goes:

select t1.id,t1.TicketNo,t1.evaluation,t1.Username
from A t1 where t1.evaluation = (
select max(evaluation)
from A t2
where t1.ticketno = t2.ticketno
)
and Username = 'Fergus'
;

We could try an index ( Username, TicketNo, evaluation desc ). It seems the
engine drives by t1, but only uses the first column of the index.
The select from view method

select t.id,t.TicketNo,t.evaluation,t.Username
from A t,
(select t2.ticketno as ticketno, max(evaluation) as evaluation
from A t2 group by t2.ticketno) as tt
where
t.ticketno = tt.ticketno and t.evaluation = tt.evaluation
and t.Username = 'Fergus'
;

appears to drive by oops, what? The explain tab has me confused.
Finally, you could try the force index and related extension of MySql to try
to force a certain driving table and index. I haven't tried this though.
And you can explicitly do the inner query first by using temporary tables.

create temporary table A2 (
ticketno int,
evaluation double
);

insert into A2 (ticketno, evaluation)
select ticketno, max(evaluation)
from A
group by ticketno;

create index A2_index on A2 ( ticketno, evaluation );

select A.id,A.TicketNo,A.evaluation,A.Username
from A inner join A2 on A.ticketno = A2.ticketno and A.evaluation =
A2.evaluation
where A.Username = 'Fergus'
;

Jul 20 '05 #4

P: n/a
On Sat, 28 Aug 2004 17:34:33 GMT, "Siemel Naran"
<Si*********@REMOVE.att.net> wrote:

Wow, that was a crash course for me. I'm kind of new to MySQL. your
explanations are very welcomes and helps me getting even more
interested in MySQL.

thanks again!!

Dennis

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.