A Bit of Query Help....

Hi,

I need to write a query or sub-query, but only use a parameter once. Take a look at this:

A     Z198051              27-AUG-07 27-AUG-07
B     Z199111              31-AUG-07 31-AUG-07
C     Z215180              17-OCT-07 17-OCT-07
D     Z27867               21-FEB-06 22-FEB-06
E     Z239557              28-DEC-07 28-DEC-07
F     Z230612              01-DEC-07 03-DEC-07
G     Z108701              22-NOV-06 23-NOV-06
H     Z171480              04-JUN-07 04-JUN-07
I     Z143538              02-MAR-07 02-MAR-07
J     Z107108              16-NOV-06 17-NOV-06
K     Z114553              18-DEC-06 19-DEC-06
L     Z168095              24-MAY-07 24-MAY-07
M     Z82875               22-AUG-06 23-AUG-06
N     Z71657               14-JUL-06 14-JUL-06
O     Z55598               19-MAY-06 19-MAY-06

Basically the code (1st column) will be passed in, and I want to retrieve the ID (second column) for that code with the MAX date (3rd column).

Problem being that the way the engine for this application is written,
you can only pass 1 parameter to it. So, I can only pass the code once, which means I can only use it in the query once......

I try things like this, but no luck:

SELECT id
FROM table1
WHERE date1 = (SELECT MAX(date1)

               FROM table1
               WHERE code = 'E' AND id LIKE 'Z%');

Any ideas? I've been playing with sub-selects and MAX(date), but it seems that I still need to put CODE in there twice......

Thanks a bunch. Received on Thu Jan 31 2008 - 20:48:09 CST