0

I have a table like this:

// mytable
+----+----------+--------------+
| id |  user_id |  reputation  |
+----+----------+--------------+
| 1  | 442      | 1000         |
| 2  | 746      | 500          |
| 3  | 843      | 800          |
| 4  | 746      | 700          |
| 5  | 442      | 300          |
+----+----------+--------------+

Here is my query:

select user_id, min(reputation) min_rep
from mytable 
group by user_id

And here is the result:

+---------+---------+
| user_id | min_rep |
+---------+---------+
| 442     | 300     |
| 746     | 500     |
| 843     | 800     |
+---------+---------+

Now I need to select the id of that row. So here is the expected result:

+----+---------+---------+
| id | user_id | min_rep |
+----+---------+---------+
| 5  | 442     | 300     |
| 2  | 746     | 500     |
| 3  | 843     | 800     |
+----+---------+---------+

Any idea how can I do that?

Martin AJ
  • 6,261
  • 8
  • 53
  • 111

1 Answers1

2

Try the below way -

select * from 
from mytable a where reputation = 
   (select min(reputation) from mytable b where a.userid=b.userid group by b.userid )
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • What about having duplicate rows in the table? For example **two rows** with these values `user_id = 442` and `reputation = 300`; – Martin AJ Jul 28 '20 at 08:41
  • I have edited the answer. In case, you have same values of userid and reputation in multiple columns, you can add 'group by a.userid' at last to get single record. – Vivek Gondhiya Jul 28 '20 at 09:04