[RESOLVED] Help with Nested Query using Inner Joins

[Spacehamster]

NOTE: This is actually for a Crystal Report, but it's the SQL Query that I'm having trouble with. Hopefully you guys (and girls) can help. Also, the table wasn't designed by me, otherwise I would've used proper primary and foreign keys.

I have a Crystal Report that needs to generate a list of stations ('location' column) that have held warrants in excess of 90 days. The report can only look at the latest movement (ie. 'received_date') for a warrant.

I have a Traffic Warrant system with two tables - Warrant and Warrant_Movements. Warrant stores the Warrant details (eg. ID etc...).

If the person being served a warrant changes location, the new location is saved in the Warrant_Movement table. Thus a single Warrant can have multiple entries in the Warrant_Movement table. These are the relevant fields:

Warrant
-----------
ID

Warrant _Movement
-------------------------
ID (same as Warrant.ID)
Received_Date (date that location changes)
Location (the new location the Warrant has moved to).

Here is the SQL Query that retrieves the latest received_date for each warrant -

select 
	warrants.id, 
	MAX(warrants_movements.received_date) As Max_Received_Date
from 
	warrants INNER JOIN
	warrants_movements ON dbo.ib_warrants.id = warrants_movements.id
WHERE
	warrants_movements.received_date Not Like '' AND
	warrants_movements.received_date IS NOT NULL AND 
	warrants.disposal_method IS NULL AND
	warrants_movements.received_date < {ts '2007-01-01 00:00:00.00'}
Group by 
	warrants.id

When I try to add the warrant_movements.location column to the SQL Query, I get this error -

Server: Msg 8120, Level 16, State 1, Line 1
Column 'dbo.ib_warrants_movements.location' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The Crystal Report needs to be grouped by location, so how can I add the location for each warrant to the query?