ORDER BY id In a subquery
I have a request like this:
SELECT i.*
FROM items i
WHERE i.id
IN (
SELECT c.item_id
FROM cart c
WHERE c.sessID=MY_SESSION_ID
)
Works great, but I need to sort the items from the cart by the date of purchase (cart.id) DESC. I don't want to sort in PHP. How can I sort cart.id?
I tried:
SELECT i.*
FROM items i
WHERE i.id
IN (
SELECT c.item_id
FROM cart c
WHERE c.sessID=MY_SESSION_ID
)
ORDER BY c.id
But it didn't sort correctly.
+3
source to share
3 answers
Change your Sub request to Inner Join
. Sub query
will not let you refer to columns outside Sub query
. So change it toInner Join
SELECT i.*
FROM items i
JOIN (SELECT item_id,
id
FROM cart) C
ON i.id = c.item_id
AND c.sessID = MY_SESSION_ID
ORDER BY c.id Desc
or use this.
SELECT i.*
FROM items i
JOIN cart C
ON i.id = c.item_id
AND c.sessID = MY_SESSION_ID
ORDER BY c.id Desc
+2
source to share
Try the following:
SELECT i.*
FROM items i
INNER JOIN cart c ON i.id = c.item_id
WHERE c.sessID = MY_SESSION_ID
GROUP BY i.id
ORDER BY MAX(c.id) DESC;
OR
SELECT i.*
FROM items i
INNER JOIN (SELECT item_id, MAX(id) AS cid
FROM cart
WHERE sessID = MY_SESSION_ID
GROUP BY item_id
) AS c ON i.id = c.item_id
ORDER BY c.cid DESC;
+1
source to share