SQL having/where nightmare 1

[fishysheep]

five in morning here and my brain's gone on strike. Can some kind soul help out before breakfast/deadline please. ta.

TABLE-1
ID
=
1
2
3

TABLE-2
ID CODE VALUE
==================
1 1 0
1 2 5
1 3 1
1 4 4
1 5 0
2 1 3
2 2 2
2 3 7
2 4 1
2 5 2
3 1 4
3 2 2
3 3 4
3 4 1
3 5 2

right, what I'm trying to do is to pull the highest VALUE from the first three records (based on Table2-ID), unless the first three records contain a VALUE of 0, or the highest VALUE is duplicated with the first three.

So, using above data the query should return:

ID CODE VALUE
==================
1 - ignore as zero in first three
2 - return CODE 3, VALUE 7
3 - ignore as highest VALUE (4) is duplicated in first three

thanks

 

[Remou]

Here is an attempt:

SELECT X.ID, Max(X.VALUE) AS MaxOfVALUE
FROM Table2x AS X
WHERE ((([ID] & [Code]) In (SELECT TOP 3 [ID] & [Code] AS Expr1
FROM Table2x Z
Where Z.ID=x.ID 
ORDER BY ID, CODE)))
GROUP BY X.ID
HAVING (((X.ID) Not In (SELECT TOP 3 [ID]
FROM Table2x Z
Where Z.ID=x.ID And z.Value=0
ORDER BY ID, CODE) And (X.ID) Not In (SELECT X.ID
FROM Table2x AS X
WHERE ((([ID] & [Code]) In (SELECT TOP 3 [ID] & [Code] AS Expr1
FROM Table2x Z
Where Z.ID=x.ID 
ORDER BY ID, CODE)))
GROUP BY X.ID, X.VALUE
HAVING (((Count(X.ID))>1) AND ((Count(X.VALUE))>1));)));

 

[fishysheep]

Remou, I'm speechless with admiration here. If that's what you consider an "attempt" I'd hate to see a full-on effort

thank you so much