Max and 2nd Highest for two identifiers

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

Guest

Hi all,

I am pretty well stumped on this one and it is hard for me to explain sooo
here is my data:


PN: Test to Pass:
1001 1.0
1001 2.3
1001 2.3
2001 2.0
2001 2.0
3002 1.2
3002 2.3
3002 2.0
3002 2.3


Lets say that the minimum testing is 2.0 to pass, but the hitch is that I
have to pass them in pairs so if I simply did a > 2.0, I would get 3 test
results for 3002 and of coarse you would want the highest test result.

So here is what the above data needs to look like:

PN: Test to Pass:
1001 2.3
1001 2.3
2001 2.0
2001 2.0
3002 2.3
3002 2.3

I am try to get by without creating a list and the user pick the test
results from it because that offers and whole new set of hairloss causing
problems .

Thanks for any help on this one.
Ham.
 
Not sure exactly of what you really want, but I would start with:

SELECT PN, test
FROM myTable
WHERE test >= 2.0
GROUP BY PN, test
HAVING COUNT(*)=2


which return:

1001 2.3
2001 2.0
3002 2.3



The
WHERE test>=2.0

removes record having the second field <2.

The

HAVING COUNT(*)=2

keeps only groups having 2 records (two records with the same value for the
second field), AFTER the WHERE clause made its clean up.




Hoping it may help
Vanderghast, Access MVP
 
Not tested, but should work.

SELECT DISTINCT PN, Max([Test To Pass]) As HighScore FROM SomeTable WHERE
[Test To Pass] >= 2.0;
 
Sorry I have a rough time explaining myself:

Each value in "test to pass" column is an indiviual test so they can't be
combined and I need the two highest passing tests per PN number.

So for example I can get part of my work by doing a max query:

Before

PN: Test:
1001 1.0
1001 2.0
1001 2.1
2001 2.3
2001 2.3
3001 1.4
3001 2.0
3001 2.3

After Max on test:

PN: Test:
1001 2.1
2001 2.3
3001 2.3

well what i need is the second highest test score as well:

PN: Test:
1001 2.1
1001 2.0
2001 2.3
2001 2.3
3001 2.3
3001 2.0

so if I do a < Max and >2.0 I could potentally get more than two tests per
PN.

Let me know if this is a clear as mud, I have dragged myself backwards and
forwards though this one.
 
There are many solutions to take the TOP n by group, I prefer a solution
based on ranking:


SELECT a.pn, a.test
FROM myTable As a INNER JOIN myTable AS b
ON a.pn=b.pn AND a.test <= b.test
WHERE a.test >=2.0
GROUP BY a.pn, a.test
HAVING COUNT(*) <= 2
ORDER BY a.pn, a.test DESC



That lists the TOP 2 test scores per PN (with a score >=2.0). Sure, if there
is only ONE test score >=2.0, per PN, that single test will be also listed.
You can write another query calling this one to remove these PN with a
single test score >=2.0


Hoping it may help,
Vanderghast, Access MVP
 
Is there a primary key in the table that can be used to differentiate
between the records? If so, you could use the TOP predicate in combination
with the test and PN number.

Something like

SELECT PN, Test
FROM YourTable as Y
WHERE Y.PK IN (
SELECT Top 2 PK
FROM YourTable as Ya
WHERE Test > = 2 and
Ya.PN = Y.PN
ORDER BY Test Desc, PK)

If you don't have a primary field or some set of fields that you can use as
a primary field, I don't see any easy solution. And even this one is liable
to be slow with a large set of records.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
*IF* a PN can have three scores all equal, and >=2.0, better to use:


SELECT a.pn, a.test
FROM myTable As a LEFT JOIN myTable AS b
ON a.pn=b.pn AND a.test < b.test
WHERE a.test >=2.0
GROUP BY a.pn, a.test
HAVING COUNT(b.test) < 2
ORDER BY a.pn, a.test DESC



Vanderghast, Access MVP
 
Wasn't thinking about the primary key, so there is a primary number generated
for each test contucted so what I need to do is set a query that gets max
test results for the entire set then excludes the max (test) records which I
have already pulled and pulles the next set of max (max) records.
 
That is certainly one way to handle it, although you said you wanted only
two records returned. The query I posted would return two records if there
were two that met the criteria - otherwise one (or none). Good luck on
working out a solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Ok Guys here is what I did, it will need some refining:

I did max value:

1001 2.3
2001 2.5
3001 2

then made another column with the same testing data with Not [max Value
Column] then preformed a max value on that column which gave me the max value
excluding the max values alread selected.

Thanks for getting me thinking straight!
Ham.
 
Back
Top