The disctinct group by query using mysql does not duplicate records

  • 2020-05-07 20:33:22
  • OfStack

There is a demand, 1 has not been solved, in google for a long time to find a solution, not one of the proposed scheme can be used, finally the devil done.
  looks like this. Suppose 1 table:

id       f_id         value      
1           2                   a      
2           2                   b      
3           5                   c      
4           9                   c      
5           9                   a      
6           6                   d    

id       f_id         value
1           2                   a
2           2                   b
3           5                   c
4           9                   c
5           9                   a
6           6                   d
  id is the primary key, f_id is the foreign key, I need to get the data of the foreign key f_id which is not repeated, if I use group   by   or distinct, it is easy to solve


select   f_id   from   table   group   by   f_id
select   distinct   f_id   from   table
  but if you try to get id in the result, it's going to be messy anyway. For example, if I want to sort the results by id, such as "select   distinct   f_id,   id from table table order order           desc" is completely in vain. After looking at a number of examples on google, I found that id needed to be manipulated in select to let mysql know what to do with id besides f_id. Such as Max,   Min,   Avg,Sun.. All is ok, then becomes the following code to solve...

select   f_id,   max(id)   as   id   from   table   group   by   f_id   order   by   id   desc
  is done, there is an article on the Internet very close to the answer, but it does not have "as   id", resulting in the wrong results in my mysql execution, hehe.

Related articles: