Complex Query 3 Tables combine ORDER BY + GROUP BY

[dlite922]

hey fellas (and ladies)

I need help with making a query.

My tables are: (Simplified)

case:
id
violatorID
number

violator:
id
name

trial:
id
caseID
date



A Case can have Multiple trials with different dates.

I need a query that gets returns the following information
caseID, caseNumber, violatorName, and the FIRST trial date.

So i need distinct cases, but it should return the earliest trial date. If there's on in March and one in July, it should return the one in March.

MY ATTEMPT:

 
SELECT c.id as NUM, c.caseNumber, v.firstName, v.lastName, t.courtDate 
FROM `case` as c, violator as v, trial as t 
WHERE c.violatorID = v.id AND t.CaseID = c.id 
GROUP BY c.id;
 
 

the above returns the last trial date (in above example, the one in July)

Then i tried this:

 
SELECT c.id as NUM, c.caseNumber, v.firstName, v.lastName, t.courtDate 
FROM `case` as c, violator as v, trial as t 
WHERE c.violatorID = v.id AND t.CaseID = c.id
ORDER BY t.courtDate 
GROUP BY c.id;
 
 
//and got...
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY c.id' at line 1
 
 
 

can't use Order By and Group By together? at least in this instance i can't.

any tips much appreciate it.

for the record, I know violator should have caseID, instead of vice versa.

[code green]

can't use Order By and Group By together? at least in this instance i can't

No, you've just got them the wrong way around.
GROUP BY has to be before ORDER BY