Oracle SQL problem

[reddevilggg]

I'm having trouble putting the finishing touch to this SQL statement...........

I have 3 locations (location 2, location 3, location 4)  and each location offers 3 services. I want the query to show each location grouped together with the revenue highest, so that you can see the highest service for each location like so.....

location 2 - Service 2 - £50
location 2 - service 1 - £30
location 2 - service 3 - £10
location 3 - service 3 - £40
location 3 - service 1 - £20
location 3 - service 2 - £10
location 4 - service 2 - £45
location 4 - service 3 - £30
location 4 - service 1 - £20

So far the statement i've got is this -

SELECT location.location_id, location.location_name, service.description, SUM(price) AS REVENUE
FROM location, service, service_line
WHERE service.location_id=location.location_id
AND service.service_id=service_line.service_id
GROUP BY location.location_id, location.location_name, service.description
HAVING SUM (price) > 0
ORDER BY revenue DESC;

Which shows the revenue in descending order but this mixes up the locations.

Can anyone point me in the right direction ?

[Rob Pomeroy]

Code: [Select]

ORDER BY location.location_name ASC,  revenue DESC;

[reddevilggg]

Code: [Select]

ORDER BY location.location_name ASC,  revenue DESC;

ah, of course, Thanks Rob 

[Rob Pomeroy]

No problem.