0

I have seen plenty of similar questions asked:

Most starred one is this one: Retrieving the last record in each group - MySQL

But my question is different.

I would like to select latest of a group (each option_id) for 1 user. Not all latest and not all latest for all users.

Imagine table called "options":

+-----+-----------+---------+-------+
| id  | option_id | user_id | value |
+-----+-----------+---------+-------+
| 100 |         3 |       2 |  1000 |
|  99 |         3 |       2 |   500 |
|  98 |         3 |       2 |  1000 |
|  97 |         2 |       2 |     2 |
|  96 |         2 |       2 |     6 |
|  95 |         1 |       2 |    88 |
|  94 |         1 |       2 |    87 |
|  93 |         3 |       2 |  1000 |
|  92 |         2 |       1 |    85 |
+-----+-----------+---------+-------+

Expected result of the query for user_id=2:

+-----+-----------+---------+-------+
| id  | option_id | user_id | value |
+-----+-----------+---------+-------+
| 100 |         3 |       2 |  1000 |
|  97 |         2 |       2 |     2 |
|  95 |         1 |       2 |    88 |
+-----+-----------+---------+-------+

Pseudo query: select latest (highest id) of each option_id where user_id=2

This is what I tried:

SELECT * 
FROM options 
where user_id =2 and id IN (
    select max(id)
    from options
    group by option_id
)

It seems like it gives expected results but only for some option_id not all. So I would get row 100 and 95 only for user_id=2, but option_id 2 is missing in results.

Dom
  • 645
  • 6
  • 17

2 Answers2

0

you could use an inner join 

select * from  options o
inner join (
  select max(id) max_id, option_id 
  from options
  group by option_id 
) t on t.max_id = o.id and t.option_id = o.option_id
where  user_id= 2
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Hi, this seems to give only partial results - just like the query I have been using in my question. – Dom Apr 10 '19 at 16:23
  • show me the exact result you obtain .. max(id) ..because . group by option_id must return a value for each option_id ... eventually check better for your real table content – ScaisEdge Apr 10 '19 at 16:25
  • User id is INT. – Dom Apr 10 '19 at 16:31
  • then check for the real content of your test data for id and option id – ScaisEdge Apr 10 '19 at 16:32
  • Triple checked my table content and the query is missing some option_id... sqlfiddle is offline, otherwise I would have exported the table for you to see. :( – Dom Apr 10 '19 at 16:40
  • this is strange anyway when you can create a valid sqlfiddle . comment me the link so i can check the same situation as yours – ScaisEdge Apr 10 '19 at 16:42
0

Turns out it was as easy as:

SELECT * FROM dbName.options where user_id =2

group by option_id

order by id desc;
Dom
  • 645
  • 6
  • 17