php query how to use "Order by" after using "Order By .... Limit"?

Question

I want to have a sort function on my php site which shows 10 newest items.

So normally shows 10 newest items which order by create date, and I would like to have sort function for these 10 items by their price.

There are totally around 100 items

table1 contains 'item number','create date'

table2 contains 'item number','price'

I first get the whole data set by join both tables, order by the create date, then get the first 10 results as newest items. At this point, it shows what I want.

$sql_All_Item="SELECT * FROM table1 LEFT JOIN table12 ON table1.ITEM_NO = table2.ITEM_NO ORDER BY table1.Create_Date DESC LIMIT 10";

but I have no idea how to make the sort function for another 'order by' on this query to get the newest 10 items and show the price descend or ascend for those 10 items.

Answer 1

You need to nest the query, and perform the final sort in the outer query:

SELECT *
FROM (
    SELECT *        -- better enumerate the columns here than use "*"
    FROM table1 t1  -- table aliases make the query easier to write and read
    LEFT JOIN table2 t2 ON t1.ITEM_NO = t2.ITEM_NO 
    ORDER BY t1.Create_Date DESC 
    LIMIT 10
) t
ORDER BY price desc