Odtnhj

Using the above data structure, I want to get back records based on the grouping of model_id, how important the record is, and how recent the record is. So for this data, I want to get back records 2, 4, 6, 7 and 10.

• I want to get back record 2 because for model_id 1, records 1 and 2 are of the same importance, but 2 is more recent.


• I want to get back record 4 because for model_id 2, record 4 has a higher importance than record 3.


• I want to get back record 6 because for model_id 3, records 5 and 6 are of the same importance, but 6 is more recent.


• I want to get back record 7 because it is the only record for model_id 4, regardless of its importance level.


• I want to get back record 10 because it has a higher importance, and it is the most recent of the higher importance records for model_id 5.

The score field isn't really important to the query, it's just the value I want to use when I get back the result set I want.

I'm using MySQL 5.6 for this.

Anyone know how to attack this?

Edit: Adding a link to db-fiddle: https://www.db-fiddle.com/f/9ZEcpn7vfBebAxnEBtfAwe/0

This is the result set I want back:

2, lower, 4.6, 1, 2018-10-02 12:00:00 
4, higher, 6.3, 2, 2018-08-13 12:00:00 
6, higher, 4.1, 3, 2018-08-23 12:00:00 
7, higher, 7.1, 4, 2018-08-11 12:00:00 
10, higher, 4.7, 5, 2018-09-14 12:00:00

This is the SQL I have that has gotten close, thanks to a clue about using DENSE_RANK(). It gives me the right result in my local MySql Workbench, but not quite right on db-fiddle.

set @pk1 ='';
set @rn1 =1;
set @sal ='';
set @val =1;

SELECT id,model_id,
 importance,
 denseRank
FROM
(
 SELECT id,model_id,
 importance,
 @rn1 := if(@pk1=model_id, if(@sal=importance, @rn1, @rn1+@val),1) as denseRank,
 @val := if(@pk1=model_id, if(@sal=importance, @val+1, 1),1) as value,
 @pk1 := model_id,
 @sal := importance 
 FROM
 (
 SELECT id,model_id,
 importance
 FROM temp
 ORDER BY model_id,importance,created_at desc
) A
) B where denseRank = '1' group by model_id; 

Unfortunately, your version of MySQL doesn't have row_number(), which is the most common method for writing prioritization queries. But you can still do this:

select t.*
from t
where t.id = (select t2.id
 from t t2
 where t2.model_id = t.model_id
 order by find_in_set(t2.importance, 'lower,higher') desc,
 created_at desc
 limit 1
 );

You can use User-defined variables to determine Row Number within a partition of model_id, as per the defined Ordering.

Field() function allows us to sort importance field accordingly. We can then simply consider all those rows where row number is equal to 1.

select dt2.*
from (
 select 
 @rn := case when @mid = dt.model_id then @rn + 1
 else 1
 end AS row_no, 
 dt.id, 
 dt.importance, 
 dt.score, 
 @mid := dt.model_id as model_id, 
 dt.created_at 
 from 
 (
 select * 
 from temp
 order by model_id, 
 field(importance, 'higher', 'lower'), 
 created_at DESC 
 ) AS dt
 cross join (select @rn := 0, 
 @mid := 0) AS user_init_vars 
 ) AS dt2 
Where dt2.row_no = 1
Order by dt2.model_id;

Result

| row_no | id | importance | score | model_id | created_at |
| ------ | --- | ---------- | ----- | -------- | ------------------- |
| 1 | 2 | lower | 4.6 | 1 | 2018-10-02 12:00:00 |
| 1 | 4 | higher | 6.3 | 2 | 2018-08-13 12:00:00 |
| 1 | 6 | higher | 4.1 | 3 | 2018-08-23 12:00:00 |
| 1 | 7 | higher | 7.1 | 4 | 2018-08-11 12:00:00 |
| 1 | 10 | higher | 4.7 | 5 | 2018-09-14 12:00:00 |

View on DB Fiddle