mysql query headaches - sorting/grouping

[BenWill]

Hey all, posting again here as got major headaches! Basically.. I have a series of quite complicated queries, but the main problem starts right at the beginning, other than that I reckon im fine.

What I want to do, is display a list of Registrations from Student's for Modules.

This is the query I have at the moment (without the group statement)

SELECT * FROM Registration WHERE Student_ID = '222222222' AND Year_of_Study = '1'  ORDER by Academic_Year DESC, Resit_exam DESC

Now this lists registrations, and shows at the top of the most recent registration that I would want to use. (I would only want to use the top Registration, most recent, and if two exist in the same year, to use the one which has been resit)

Just to show heres the output from above:

mod ID regID acc year resit
COMP104 29 2008 1 <<< this one should be the one used when grouped
COMP107 22 2008 1
COMP108 23 2008 0
COMP106 21 2008 0
COMP105 20 2008 0
COMP104 19 2008 0 <<< this one is used when grouped by module_Id
COMP103 18 2008 0
COMP102 17 2008 0
COMP101 16 2008 0
COMP104 28 2007 0 <<< another registration for this module

However when I group by Module_ID - it selects the oldest registration in the query. I can group by Registration ID, but this then obviously creates 3 entries for Module_ID (One for each Registration-, whereas I only want the most recent entry)

Hope im clear enough, staying up till silly hours trying different things making me lose it a bit! Cheers for any help/advice

[nav33n]

SELECT *
FROM registration
WHERE student_ID = '222222222'
AND year_of_Study = '1'
GROUP BY mod_id
ORDER BY academic_year DESC , resit_exam DESC
LIMIT 0 , 30

Doesn't this work ?

[BenWill]

Afraid not, did the query with the LIMIT addition and have the same problem, even tried sorting it by Registration ID. It seems to group before it sorts, anyway around this? or anyway to do this differently?

Heres the output:

SELECT * FROM Registration WHERE Student_ID = '222222222' AND Year_of_Study = '1' GROUP BY Module_ID ORDER BY Academic_Year DESC, Resit_exam DESC LIMIT 0 , 30

mod ID regID accyear resit
COMP107 22 2008 1
COMP103 18 2008 0
COMP104 19 2008 0
COMP105 20 2008 0
COMP106 21 2008 0
COMP108 23 2008 0
COMP101 16 2008 0
COMP102 17 2008 0

[nav33n]

Hmm..Strange.. I created a test table and tried the query with group by clause and it worked ! You should have posted this question in mysql forum(for better response).

[BenWill]

Managed work out a solution, probably not the best codewise, but simply within my while loops created an if statement, sorted the list by modules, and if a new var $last = the current row's module id, it skips to next loop, amount time took messing with the sorting/grouping when one little if statement fixed things for me! Argh! Thanks though nav33n, not sure either why your table worked and mine didnt, as without the group it showed the relevant results in the right order (altho extras).. and with the group it just showed the first id, i assume due to the order, it does actually group before it sorts

[nav33n]

hmm.. Anyway, you solved your problem and congrats for that! :)