Extract maximums

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there... I'm starting this thread from scratch, cos I'm not coming right
with current solutions: My problem in a nutshell - I have the following
records:
ShadeRecName ShadeRecVer ChemName
BAMBOO 10 M1 1 TUBINGAL LM - L
BAMBOO 10 M1 1 ACETIC ACID
BAMBOO 10 M1 1 FELOSAN XNF -
BAMBOO 10 M1 1 ACETIC ACID
BAMBOO 10 M1 1 SODA ASH 1
BAMBOO 10 M1 1 SARABID C12 - L
BAMBOO 10 M1 1 RESPUMIT NF
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 TANNEX GEO\
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 TUBINGAL LM - L

What I would like to do is extract all records where ShadeRecVer is at its
maximum value. My Access query (in SQL) is as follows:

SELECT R.ShadeRecName, R.ShadeRecVer, CHEMICALS.ChemName
FROM (SHADE_RECIPES AS R LEFT JOIN CHEMICALS R.ActionID = CHEMICALS.ChemID
WHERE (R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer) FROM SHADE_RECIPES AS R2
WHERE R2.ShadeRecVer = R.ShadeRecVer))
ORDER BY R.ShadeRecName, R.ShadeRecVer;
 
Dear Fox:

You do now say whether the query you posted is working or not, or what the
problem might be with it.

I found it has unbalanced parentheses. I fixed that and made some
readibility changes to help me study it.

SELECT R.ShadeRecName, R.ShadeRecVer, C.ChemName
FROM SHADE_RECIPES AS R LEFT JOIN CHEMICALS C R.ActionID = C.ChemID
WHERE R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer) FROM SHADE_RECIPES AS R2
WHERE R2.ShadeRecVer = R.ShadeRecVer)
ORDER BY R.ShadeRecName, R.ShadeRecVer;

The next thing is, this makes no sense. The subquery is correlated on
ShadeRecVer and is then used to filter ShadeRecVer. From your previous
thread, I thought you wanted the maximum of the ShadeRecVer over
ShadeRecName. That would be:

SELECT R.ShadeRecName, R.ShadeRecVer, C.ChemName
FROM SHADE_RECIPES AS R LEFT JOIN CHEMICALS C R.ActionID = C.ChemID
WHERE R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer) FROM SHADE_RECIPES AS R2
WHERE R2.ShadeRecName = R.ShadeRecName)
ORDER BY R.ShadeRecName, R.ShadeRecVer;

That is, the maximum ShadeRecVer is taken across all rows with the same
ShadeRecName. Is that what you really intend?

For those of us trying to answer your question, it is much easier if this
stays together in one thread instead of starting a new one. OK? To figure
what was wrong I had to refer to your previous postings. Obviously, that's
easier when its all together.

Tom Ellison
 
Dear Fox:

I apologize for correcting you on starting a new thread. I see that you did
give me a chance to see this problem in your existing thread. I started
with the newest posts in the newsgroup and worked my way back to where I saw
that you did do what I'd asked you to do, which is to post to the original
thread. No need to start a new one, though.

Tom Ellison
 
Hi Tom,
I apologise for jumping threads, but I wasn't aware that you are also
receiving email notifications of this...
Anyways, Firstly, Access adds the parenthesis. I've taken them all out to
read as you suggested, but it still doesn't extract the max of ShadeRecVer.
Secondly, yes I want to extract all records where ShadeRecVer is a maximum
over ShadeRecName.
My query currently looks like this: (this is the complete query with all
other tables and fields in)

SELECT R.ShadeRecName, R.ShadeRecVer, R.ShadeRecID, STEPS.StepOrder,
STEPS.StepType, CHEMICALS.ChemName, STEPS.ChemGpl, STEPS.ChemPercent,
CHEMICALS.Price
FROM (SHADE_RECIPES AS R LEFT JOIN STEPS ON R.MethodID = STEPS.MethodID)
LEFT JOIN CHEMICALS ON STEPS.ActionID = CHEMICALS.ChemID
WHERE STEPS.StepType="CHM" AND R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer)
FROM SHADE_RECIPES AS R2 WHERE R2.ShadeRecName = R.ShadeRecName)
ORDER BY R.ShadeRecName, R.ShadeRecVer;

Thank you very much for the help so far...
Regards,
 
Dear Fox:

Well, I do try to track whatever's new in the newsgroup, and expecially in
threads where I've participated.

Please try this query. It will eliminate some of the complexity, and
possibly find what's happening:

SELECT R.ShadeRecName, R.ShadeRecVer, R.ShadeRecID
FROM SHADE_RECIPES AS R
WHERE R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer)
FROM SHADE_RECIPES AS R2
WHERE R2.ShadeRecName = R.ShadeRecName)
ORDER BY R.ShadeRecName, R.ShadeRecVer

I have omitted the other joined tables and their columns. This could be the
source of apparent error coming from something other than the specific thing
we've been working on.

Another thing that may be instructive:

SELECT ShadeRecName, MAX(ShadeRecVer)
FROM SHADE_RECIPES
GROUP BY ShadeRecVer
ORDER BY ShadeRecName

Does this give the expected maximum version numbers for each name?

Tom Ellison
 
Hi Tom
This already looks better. But I need to clarify again, cos maybe we're
misunderstanding each other. I need this query to extract (as per the earlier
records shown), e.g.
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 TANNEX GEO
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 TUBINGAL LM -

In other words, all records for a specific name where the version = 2 (the
maximum for this specific recipe). The next colour might be BAMBOO 21 M1,
version 5, etc. What it's doing with the query you replied to just now is
BAMBOO 10 M1, ver 1; BAMBOO 10 M1, ver 2; BAMBOO 21 M1, ver 1; BAMBOO 21 M1,
ver 2; BAMBOO 21 M1, ver 3; BAMBOO 21 M1, ver 4; BAMBOO 21 M1, ver 5;

Regards,
CyberFox
 
Building on Tom Ellison's version, I got this:

SELECT R.ShadeRecName, R.ChemName, R.ShadeRecVer
FROM SHADE_RECIPES AS R INNER JOIN SHADE_RECIPES AS R2
ON R.ShadeRecName = R2.ShadeRecName
GROUP BY R.ShadeRecName, R.ChemName, R.ShadeRecVer
HAVING (((R.ShadeRecVer)=(Max([R2].[ShadeRecVer]))))
ORDER BY R.ShadeRecName, R.ChemName;

It produces (from your original data) the following:

ShadeRecName ShadeRecVer ChemName
------------ ----------- -------------
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 TANNEX GEO\
BAMBOO 10 M1 2 TUBINGAL LM - L

Is this close to what you wanted?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi Tom
This already looks better. But I need to clarify again, cos maybe we're
misunderstanding each other. I need this query to extract (as per the earlier
records shown), e.g.
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 TANNEX GEO
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 TUBINGAL LM -

In other words, all records for a specific name where the version = 2 (the
maximum for this specific recipe). The next colour might be BAMBOO 21 M1,
version 5, etc. What it's doing with the query you replied to just now is
BAMBOO 10 M1, ver 1; BAMBOO 10 M1, ver 2; BAMBOO 21 M1, ver 1; BAMBOO 21 M1,
ver 2; BAMBOO 21 M1, ver 3; BAMBOO 21 M1, ver 4; BAMBOO 21 M1, ver 5;

Regards,
CyberFox

:

Dear Fox:

Well, I do try to track whatever's new in the newsgroup, and expecially in
threads where I've participated.

Please try this query. It will eliminate some of the complexity, and
possibly find what's happening:

SELECT R.ShadeRecName, R.ShadeRecVer, R.ShadeRecID
FROM SHADE_RECIPES AS R
WHERE R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer)
FROM SHADE_RECIPES AS R2
WHERE R2.ShadeRecName = R.ShadeRecName)
ORDER BY R.ShadeRecName, R.ShadeRecVer

I have omitted the other joined tables and their columns. This could be the
source of apparent error coming from something other than the specific thing
we've been working on.

Another thing that may be instructive:

SELECT ShadeRecName, MAX(ShadeRecVer)
FROM SHADE_RECIPES
GROUP BY ShadeRecVer
ORDER BY ShadeRecName

Does this give the expected maximum version numbers for each name?

Tom Ellison
Click to expand...
[...]
Click to expand...
[...]
 
This is exactly what I wanted!
I added this logic into my original query (the one joining other tables,
etc.) and voila! it works!

Thank you all for your help!
Regards,


Vincent Johns said:
Building on Tom Ellison's version, I got this:

SELECT R.ShadeRecName, R.ChemName, R.ShadeRecVer
FROM SHADE_RECIPES AS R INNER JOIN SHADE_RECIPES AS R2
ON R.ShadeRecName = R2.ShadeRecName
GROUP BY R.ShadeRecName, R.ChemName, R.ShadeRecVer
HAVING (((R.ShadeRecVer)=(Max([R2].[ShadeRecVer]))))
ORDER BY R.ShadeRecName, R.ChemName;

It produces (from your original data) the following:

ShadeRecName ShadeRecVer ChemName
------------ ----------- -------------
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 TANNEX GEO\
BAMBOO 10 M1 2 TUBINGAL LM - L

Is this close to what you wanted?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi Tom
This already looks better. But I need to clarify again, cos maybe we're
misunderstanding each other. I need this query to extract (as per the earlier
records shown), e.g.
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 TANNEX GEO
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 TUBINGAL LM -

In other words, all records for a specific name where the version = 2 (the
maximum for this specific recipe). The next colour might be BAMBOO 21 M1,
version 5, etc. What it's doing with the query you replied to just now is
BAMBOO 10 M1, ver 1; BAMBOO 10 M1, ver 2; BAMBOO 21 M1, ver 1; BAMBOO 21 M1,
ver 2; BAMBOO 21 M1, ver 3; BAMBOO 21 M1, ver 4; BAMBOO 21 M1, ver 5;

Regards,
CyberFox

:

Dear Fox:

Well, I do try to track whatever's new in the newsgroup, and expecially in
threads where I've participated.

Please try this query. It will eliminate some of the complexity, and
possibly find what's happening:

SELECT R.ShadeRecName, R.ShadeRecVer, R.ShadeRecID
FROM SHADE_RECIPES AS R
WHERE R.ShadeRecVer = (SELECT MAX(R.ShadeRecVer)
FROM SHADE_RECIPES AS R2
WHERE R2.ShadeRecName = R.ShadeRecName)
ORDER BY R.ShadeRecName, R.ShadeRecVer

I have omitted the other joined tables and their columns. This could be the
source of apparent error coming from something other than the specific thing
we've been working on.

Another thing that may be instructive:

SELECT ShadeRecName, MAX(ShadeRecVer)
FROM SHADE_RECIPES
GROUP BY ShadeRecVer
ORDER BY ShadeRecName

Does this give the expected maximum version numbers for each name?

Tom Ellison
Click to expand...
[...]
Hi there... I'm starting this thread from scratch, cos I'm not coming
right
with current solutions: My problem in a nutshell - I have the following
records:
ShadeRecName ShadeRecVer ChemName
BAMBOO 10 M1 1 TUBINGAL LM - L
BAMBOO 10 M1 1 ACETIC ACID
BAMBOO 10 M1 1 FELOSAN XNF -
BAMBOO 10 M1 1 ACETIC ACID
BAMBOO 10 M1 1 SODA ASH 1
BAMBOO 10 M1 1 SARABID C12 - L
BAMBOO 10 M1 1 RESPUMIT NF
BAMBOO 10 M1 2 DEPICOL RC 9
BAMBOO 10 M1 2 TANNEX GEO\
BAMBOO 10 M1 2 RESPUMIT NF
BAMBOO 10 M1 2 SARABID C12 - L
BAMBOO 10 M1 2 MEROPAN EF
BAMBOO 10 M1 2 ACETIC ACID
BAMBOO 10 M1 2 TUBINGAL LM - L

What I would like to do is extract all records where ShadeRecVer is at
its
maximum value.
Click to expand...
Click to expand...
[...]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extracting maximums 4

Back
Top