php - SQL MAX and same coupon

378

I'm using this sql line to pull latest coupon:

SELECT * 
FROM   `coupons` 
WHERE  `sku` = '123456' 
       AND `id` IN (SELECT Max(id) 
                    FROM   `coupons` 
                    GROUP  BY coupon) 
ORDER  BY `id` DESC 
LIMIT  3 

It's working good, but in rare situations when i have same coupon code on different sku the results is nothing.

How can i solve this?

Thanks

102

Answer

Solution:

Try this instead:

SELECT c1.*
FROM `coupons` AS c1
INNER JOIN
(
   SELECT coupon, MAX(id) AS LatestId
   FROM `coupons` 
   where `sku` = '123456' 
   GROUP BY coupon
) AS c2 ON c1.coupon = c2.coupon AND c1.id = c2.LatestId
where c1.`sku` = '123456';

This way the inner query will give you the max id for eachcoupon and then the inner join will remove all expect those coupon with the max id.

In your query usingIN predicate, you are comparing the max id for any coupon in the outer query, not only those max ids for each coupon.

944

Answer

Solution:

Why not just do this?

SELECT c.* 
FROM coupons c
WHERE c.sku = '123456' 
ORDER BY id DESC 
LIMIT 3 ;

People are also looking for solutions to the problem: php - Wordpress show result posts from query

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.