CODEWITHSUNDEEP
mysql
Rez
Rez

Reputation: 524

Return last matched row in mysql

I have this table 

 id | qty_from | qty_to | p_id | price 
--------------------------------------
 1  |    1     |   10   |  4   | 1000  
--------------------------------------
 2  |    11    |   20   |  4   | 2000  
--------------------------------------
 3  |    1     |   10   |  5   | 500   
--------------------------------------
 4  |    11    |   20   |  5   | 1000  
--------------------------------------
 5  |    1     |   10   |  6   | 1000  
--------------------------------------
 6  |    10    |   15   |  6   | 2000

And i tried to get rows by qty_from AND qty_to AND p_id using below code 

SELECT * FROM table WHERE p_id IN ('4', '5', '6') AND qty_from <= 8 AND qty_to >= 8

It returns 1st, 3rd and 5th rows.
And when i use this code, it return only 1st and 3rd rows.

SELECT * FROM table WHERE p_id IN ('4', '5', '6') AND qty_from <= 16 AND qty_to >= 16

I want to return 6th row too. because it's the biggest qty in p_id = 6
How can i achieve this?

Upvotes: 0

Views: 66

Answers (4)

Nils Nilsnsn
Nils Nilsnsn

Reputation: 1

This solution works:

SELECT DISTINCT t3.*
FROM `table` t1
LEFT JOIN `table` t2 ON t2.id=IFNULL(
    (SELECT id FROM `table` t3 
     WHERE t3.p_id=t1.p_id AND 16 BETWEEN t3.qty_from AND t3.qty_to 
     ORDER BY qty_to DESC LIMIT 1),
    (SELECT id FROM `table` t4 
     WHERE t4.p_id=t1.p_id 
     ORDER BY qty_to DESC LIMIT 1))
WHERE t1.p_id IN ('4', '5', '6')

Basically, first a table with the given p_ids is fetched and then joined with the rows of the most desired ids per p_id (if 16 is not in range of qty_from and qty_to, the one with the biggest qty_to is taken).

One flaw: If multiple rows match the range condition, only the one with the biggest qty_to is selected, so there is only one result per p_id. I hope that's sufficient for you!

Upvotes: 0

Rez
Rez

Reputation: 524

I just solve this.
Thanks to @Meloman for giving me the clue.

SELECT * FROM table WHERE p_id IN ('4', '5', '6') AND ((qty_from <= 16 AND qty_to >= 16) OR (qty_to, p_id) IN (SELECT dd.qty_to, dd.p_id FROM table dd INNER JOIN (SELECT MAX(qty_to) AS mm, p_id FROM table GROUP BY p_id) ddd ON dd.p_id = ddd.p_id AND dd.qty_to = ddd.mm WHERE dd.p_id IN ('4', '5', '6'))) GROUP BY p_id

I test this multiple times and i think it's the answer

Upvotes: 0

Meloman
Meloman

Reputation: 3712

After discuss here is what you need (first and not tested solution) :

SELECT * 
FROM (SELECT *, MAX(qty_to) as max_qty_to FROM `limit`) T
WHERE p_id IN ('4', '5', '6') 
AND ( ( qty_from <= 16 AND qty_to >= 16 ) OR qty_to = T.max_qty_to )

Upvotes: 1

Rahul Khandelwal Systematix
Rahul Khandelwal Systematix

Reputation: 141

You should update your where condition to achieve your desired result. The updated query will be - 

SELECT * FROM table WHERE p_id IN ('4', '5', '6') AND qty_from <= 10 AND qty_to >= 8

Upvotes: 0

Related Questions