How to get unique rows in mysql with a column of type varchar

Question

I have a table, named as 'person_data':-

    id   req_type    reqp_id reqt_id  dev_id
    1    track       11     12      33
    2    stop_track  11     12      33
    3    track       12     13      44
    4    stop_track  12     13      44
    5    track       12     13      45
    6    track       13     14      55

Now the condition is i want:-

1. Only last row for those rows which have "reqp_id, reqt_id, dev_id" column duplicate.
2. All rows if they don't have these three column as duplicate. and situation is something like we can not use IN operator.

What i tried is :-

select max(id),min(req_type),reqp_id, reqt_id, dev_id 
from person_data group by reqp_id, reqt_id, dev_id;

and the output is exactly same as i want:-

id  req_type    reqp_id reqt_id dev_id
2   stop_track  11      12      33
4   stop_track  12      13      44
5   track       12      13      45
6   track       13      14      55

and if i used:-

select max(id),max(req_type),reqp_id, reqt_id, dev_id 
from person_data group by reqp_id, reqt_id, dev_id;

then the output changed in only "req_type" column as :-

req_type 
track
track 
track
track

But output is clearly describing that max or min function sorting the values using alphabetical order that i don't want otherwise if someone change the values of "req_type" column then output will be changed. thanks in advance,

Answer 1

Try this

SELECT T.* FROM person_data T JOIN
(
  SELECT Max(id) as maxid,reqp_id, reqt_id, dev_id 
  FROM person_data 
  GROUP BY reqp_id, reqt_id, dev_id
) S
ON T.id = S.maxid

Fiddle Demo

Output would be:

---

id  req_type    reqp_id reqt_id dev_id
2   stop_track  11      12      33
4   stop_track  12      13      44
5   track       12      13      45
6   track       13      14      55