SELECT pack_id ,speed , monthly_payment
FROM acdb_packages 
WHERE monthly_payment > ALL(SELECT monthly_payment FROM acdb_packages WHERE speed = '5Mbps');

select p1.pack_id, p1.speed, p1.monthly_payment
from acdb_packages p1
where p1.monthly_payment > (select max(monthly_payment) from acdb_packages p2 where p2.speed ='5Mbps');
​

To change first code by using max instead of ALL. I WROTE second codes. The result shows both of them with same number of rows. BUt the difference is the order. The result shows that
the order of First one is monthly_payment asc the order of Second one is pact_id asc. Is there any problems in second code? i also want same order. Somebody help me please.

CodePudding user response：

If you want a ordered result you need to add an ORDER BY clause. Otherwise your result is in "random" sort order. There are some rules that autosort your result but it's bad practice to hope the sql engine sorts things if you do not specify that you want a sorted result.

So you could just add at the end ORDER BY 3 which is short for order the result by the 3rd result column or better add ORDER BY monthly_payment

CodePudding user response：

If you want the results of a query to display in a specific order then you need to use an ORDER BY clause. Relational DBMSs have no built-in record order and the order of records is random - they are normally returned in PK order but this should not be assumed or relied upon