How to do custom sorting in Oracle?

Question

Oracle query returning the values as like below example.but i want order which contact name as admin,jack,robert , but below order is not alphabetical and same time prodid should be same groups like below example.

Oracle Query:

SELECT part_id, prodname, shippingaddress, contact 
FROM part p, address a WHERE p.part_id = a.part_id (+) 
AND p.quarter = a.quarter (+) AND 
p.quarter = '2014' AND order by p.prod_id

Above query output:

prodid     prodname    shippingaddress    contact
-------------------------------------------------
01         computer    xxxxxx             Jack
01         computer    xxxxxx             admin
01         computer    xxxxxx             robert
03         keybord     xxxxxx             admin
03         keybord     xxxxx              jack
06         cpu         xxxx               robert
06         cpu         xxxx               admin 
06         cpu         xxx                jack 

Expected output:

prodid     prodname    shippingaddress    contact
-------------------------------------------------
01         computer    xxxxxx             admin
01         computer    xxxxxx             jack
01         computer    xxxxxx             robert
03         keybord     xxxxxx             admin
03         keybord     xxxxx              jack
06         cpu         xxxx               admin
06         cpu         xxxx               jack
06         cpu         xxx                robert

Is this possible in SQL ?

Answer 1

You can define multiple levels of ORDER:

SELECT part_id, prodname, shippingaddress, contact 
FROM part p, address a WHERE p.part_id = a.part_id (+) 
AND p.quarter = a.quarter (+) AND 
p.quarter = '2014' AND order by p.prod_id, contact

Using updated JOINS something like:

SELECT part_id, prodname, shippingaddress, contact 
FROM part p
JOIN address a 
   ON p.part_id = a.part_id
   AND p.quarter = a.quarter
WHERE p.quarter = '2014' 
ORDER BY p.prod_id, contact

Answer 2

try

SELECT part_id, prodname, shippingaddress, contact 
FROM part p, address a WHERE p.part_id = a.part_id (+) 
AND p.quarter = a.quarter (+) AND 
p.quarter = '2014' order by p.prod_id, contact

BEWARE: Your query uses old join syntax... try to avoid that and to use newer join syntax (look for inner join, outer join etc. instead of (+) syntax).