Display record in X order

[dcjones]

Hi all,

This is one of those posts where I have been looking at the problem for far to long and I need to bounce it around with you guys and girls.

I have a table with the following structure:

Feild 1 = ID
Feild 2 = staff_name
Feild 3 = staff_position
Feild 4 = depart_id
Feild 5 = staff_p_no

I need to display the query result in the following way:

All the staff members in department 1 in staff_position order and so on.

This is what I am using at the moment:

SELECT *
FROM ley_staff
ORDER BY depart_id ASC, staff_p_no

Anyone with some good advice.

[Roger_Ramjet]

You need to include a GROUP BY clause to create the grouping you want

SELECT * 
FROM ley_staff 
GROUP BY depart_id, staff_p_no, staff_position, ID, staff_name
ORDER BY depart_id ASC, staff_p_no 

NB standard SQL requires all selected fields to be included in the GROUP BY clause but the latest versions of MySQL do not always require this. Read the manual for an explanation.