Hi

i have a table
with columns

student_number,
test1score,
test1date,
test2score,
test2date,
test3score,
test3date

i need to select the max(test1score) and the corresponding test1date
max(test2score) and corresponding test2date and the max(test3score) and corresponding test3date
all in one single query

also no restrictions on number of times a student takes a test
and test scores and dates can be NULL.

anyone any tips ?

I think this is what you were looking for. I tried it a couple different ways. From what you wrote i'm figuring you wanted the max test 1 score from all records and so on and soforth. This worked for me. If this isn't what you wanted, repost with more information and i'll try and help. Here is the sequel string.


SELECT Max(Query1.test1score) AS MaxOftest1score, Max(Query1.Student_Number) AS MaxOfStudent_Number, Max(Query1.test1date) AS MaxOftest1date
FROM Query1;

Hi bjackman

well thats not what i need i am posting a few values of the table
i hope my query will be clear
after that

the corresponding columns are

sid, test1scr,test1dt, test2scr,test2dt,test3scr, test3dt

("111", 300, "01/01/2004", 200, "01/02/2004", 300, "12/31/2005");
("111", 100, "01/01/2004", 200, "01/02/2004", 300, "12/31/2005");
("111", 100, "01/11/2004", 400, "01/03/2004", 233, "01/31/2005");
("111", 50, "01/01/2004", 200, "11/02/1998", 555, "11/31/2005");
("111", 50, "01/01/2009", 112, "11/02/1999", 555, "11/31/2002");

("222", 900, "01/01/2004", 600, "01/02/2004", 700, "12/31/2005");
("222", 100, "11/01/2004", 200, "10/02/2004", 700, "11/01/2002");
("222", 900, "06/01/2001", 550, "01/02/2004", 300, "12/31/2005");
("222", 100, "01/01/2004", 550, "11/02/2002", 300, "12/31/2005");
("333", 700, "07/01/2004", 600, "01/02/2004", 300, "12/31/2005");
("333", 700, "09/01/2004", 200, "11/02/2004", 150, "07/29/2005");
("333", 100, "01/01/2004", 600, "08/08/2002", 444, "06/09/2001");
("333", 100, "01/01/2004", 200, "01/02/2004", 444, "12/31/2005");

now for example sid = 111 max test 1 score will be 300
which was taken on 01/01/2004
ur query will give me 01/01/2009 which is the max test date which may not correspond to the max test score

so for id 111 i will get

111, 300, 01/01/2004, 400, "01/03/2004",555, "11/31/2005"

note that for test 3 there are 2 scores of 555 so i need the highest
date 11/31/2005

Thanks