Ordering, Grouping - HELP!

[teddysnips]

Having recently had excellent service here (many thanks, Erland!),
here's another wee problem.

SQL 2000

I need to get a report which will display the movement of people thus:

ID DIRECTION NAME DATE VEHICLE REGISTRATION

The value of DIRECTION can be either "Inbound" or "Outbound"

I need to have the data grouped by ID, with the "Outbound" row first.
For example

1 "Outbound" Smith 10/02/2005 ABC123
1 "Inbound" Smith 11/02/2005 ABC123

5 "Outbound" Jones 14/02/2005 XYZ789
5 "Inbound" Jones 15/02/2005 DEF456

This is the SQL so far.

SELECT
fldPersonID,
tblMovementType.fldType AS [MovementType],
'Outbound' AS [Direction],
fldMovementDate AS [Date],
CASE WHEN tblPerson.fldForenames IS NULL
THEN fldSurname
ELSE fldSurname + ', ' + fldForenames END AS [Name],
'Outbound ' + fldVehicleOut AS VehicleRegistration
FROM
tblPerson
INNER JOIN tblMovementType
ON tblMovementType.fldMovementTypeID = tblPerson.fldMovementType
WHERE
((fldMovementDate BETWEEN @FromDate AND @ToDate) AND
(fldStatus = 1))

UNION ALL

SELECT
fldPersonID,
tblMovementType.fldType AS [MovementType],
'Inbound' AS [Direction],
fldMovementDate AS [Date],
CASE WHEN tblPerson.fldForenames IS NULL
THEN fldSurname
ELSE fldSurname + ', ' + fldForenames END AS [Name],
'Inbound '+ fldVehicleRtn AS VehicleRegistration
FROM
tblPerson
INNER JOIN tblMovementType
ON tblMovementType.fldMovementTypeID = tblPerson.fldMovementType
WHERE
((fldMovementDate BETWEEN @FromDate AND @ToDate) AND
(fldStatus = 1))

What I need is the way to Order or Group it so that I can just put the
rows into a report without any grouping on the report itself (Crystal
Reports aarrggh!) - if this is possible!

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
Thanks

[Hugo Kornelis]

On 10 Mar 2005 02:44:13 -0800, te********@hotmail.com wrote:

(snip)

I need to get a report which will display the movement of people thus:

ID DIRECTION NAME DATE VEHICLE REGISTRATION

The value of DIRECTION can be either "Inbound" or "Outbound"

I need to have the data grouped by ID, with the "Outbound" row first.
For example

1 "Outbound" Smith 10/02/2005 ABC123
1 "Inbound" Smith 11/02/2005 ABC123

5 "Outbound" Jones 14/02/2005 XYZ789
5 "Inbound" Jones 15/02/2005 DEF456

This is the SQL so far.

(snip)

Based on what you post, I think you only need to add
ORDER BY fldPersonID, MovementType DESC
at the end of your current query to get the rows in the required order.

You mention grouping as well, but I see nothing in your post to indicate
that this is needed. Of course, I could be wrong. If the suggestion
above doesn't help, then please provide more details about your problem:
* Table structure, as CREATE TABLE statements (including all constraints
but excluding irrelevant columns -see www.aspfaq.com/5006);
* Some rows of sample data (as INSERT statements);
* The expected output, based on the sample data;
* A short but concise description of the problem you're trying to solve.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)