Get the last row from which one of its column values ​​is less than the rows of interest in the set

Question

It’s hard to explain, so I’ll break it ... Here is the goal

Suppose you have a table

ID | Weight 1 2 2 4 3 8 4 66 5 11 6 44 7 33 

And let me have a set of interested identifiers, say (3,4)

My goal is to get two other lines (one for each of the two interested identifiers), so that the line corresponding to the interested identifier has a weight that is one level less than the weight of the identifier of interest

therefore in this case

for id 3, we want to return a line with identifier 2 and weight 4, since line id 2 is the first line whose weight (4) is less than the weight of line id 3 (8)

for id 4, we want to return a line with id 6 and weight 44, since line id 6 is the first line whose weight (44) is less than the weight of line id 4 (66)

How would you do this with mysql in one query, in which we use the IN () notation for interested identifiers .....

Answer 1

I would like to suggest the following (obviously, our table is used as a table name)

 SELECT id,weight FROM ourtable WHERE weight IN (SELECT MAX(t.weight) FROM ourtable t,ourtable t2 WHERE t.weight < t2.weight && t2.id IN (3,4) GROUP BY t2.id); 

gives the following result:

 +----+--------+ | id | weight | +----+--------+ | 2 | 4 | | 6 | 44 | +----+--------+ 

upon request.

Answer 2

You can solve this by selecting the first row to select the rows sorted by desc mass, the weight of which is below the given weight, in this case for mysql something like:

 select * from t where weight < (select weight from t where id = :id) order by weight desc limit 1 

in the in expression in accordance with the above idea, you can have something like:

 select * from (select id, (select weight from t where weight < (select weight from t where id = tp.id) order by weight desc limit 1) from t tp) a where id in (3,4) 

Answer 3

Another solution without a subquery:

 select w1.id,w1.weight, left(group_concat(w2.id order by w2.id desc ),LOCATE(',', group_concat(w2.id order by w2.id desc ))-1) as w2_id, left(group_concat(w2.weight order by w2.weight desc ),LOCATE(',', group_concat(w2.weight order by w2.weight desc ))-1) as w2_weight from weight as w1, weight as w2 where w2.weight < w1.weight and w1.id in (3,4) group by w1.id