SQL to prefer one record over another

Question

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;

No images please. Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) — Madhur Bhaiya, Nov 13 '18 at 17:06
You might want to read this: https://stackoverflow.com/questions/1895110/row-number-in-mysql — Richard Hansell, Nov 13 '18 at 17:09
@jlyles can you add the expected output based on the given sample dataset to the question. — Madhur Bhaiya, Nov 13 '18 at 17:52

Gordon Linoff · Accepted Answer · 2018-11-13T21:56:20.493

3

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
             );

edited Nov 13 '18 at 21:56

answered Nov 13 '18 at 17:09

Gordon Linoff

1,242,037
58
646
786

This will work provided that the importance is actually stored as a numeric / decimal. Row_number won't help with this. The problem in 5.6 is that there are no windowing functions, although people have made a lot of stored procedures to emulate them. What he is looking for is a RANK or DENSE RANK function over max(date) grouped by ID. – T Gray Nov 13 '18 at 17:20
Thanks Gordon. I tried that query and got back 'OK' as my result set. – jlyles Nov 13 '18 at 17:29
@MadhurBhaiya It means I literally got back as my response the text 'OK' I didn't get a regular result set back, just the text 'OK'. – jlyles Nov 13 '18 at 17:51
@TGray Thanks for the dense_rank() clue. MySQL 5.6 doesn't implement it, but that clue led me to find this: https://www.folkstalk.com/2013/03/grouped-dense-rank-function-mysql-sql-query.html where someone implemented their own version of dense_rank(). I think the SQL I added to the original description will do the trick. – jlyles Nov 13 '18 at 19:12
@jlyles . . . You don't need variables for this. The subquery method should work fine. – Gordon Linoff Nov 13 '18 at 19:16
I tried that but still get 'OK' as my result set, instead of getting back the 5 rows. I updated my db-fiddle with your query and it's complaining about the subquery returning more than 1 row. https://www.db-fiddle.com/f/9ZEcpn7vfBebAxnEBtfAwe/3 – jlyles Nov 13 '18 at 19:36
@jlyles . . . You don't get "OK", you get a syntax error. If you had specified that, it would have been easy to catch that the original version left out `limit 1`, the point of the subquery. You might want to try it again. – Gordon Linoff Nov 13 '18 at 21:57
@GordonLinoff Thanks for the follow up. I tried it again and got back the result set I expected. I misspoke when saying I got back 'OK' as the result set. I got it back as the response from the database. I've never seen that response from the database before, and didn't recognize it as the database letting me know I had a syntax error. I'm used to seeing "Error Code: xxxx. You have an error in your SQL syntax..." Thanks again. – jlyles Nov 14 '18 at 15:02

score 1 · Answer 2 · answered Nov 13 '18 at 19:34

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

SQL to prefer one record over another

2 Answers2