MySQL SELECT unique column where other column is m

问题:

I have table like this

  id     |     serial_num     |      version     | .....
  1      |         1          |          1       | .....
  2      |         2          |          1       | .....
  3      |         2          |          2       | .....
  4      |         3          |          1       | .....
  5      |         4          |          1       | .....
  6      |         5          |          1       | .....
  7      |         5          |          2       | .....
  8      |         5          |          3       | .....

Now what I want to select is to get rows with max version and unique serialn_num ...

The result would be:

  id     |     serial_num     |      version     | .....
  1      |         1          |          1       | .....
  3      |         2          |          2       | .....
  4      |         3          |          1       | .....
  5      |         4          |          1       | .....
  8      |         5          |          3       | .....

My SQL is a bit more complicated and that is why I don't solve the problem by using MAX()... I have few left joins etc ...

any ideas?

Best regards and thank you for your time!

回答1:

Try this:

SELECT yourtable.*
FROM yourtable
WHERE (serial_num, version) in (select serial_num, max(version)
                                from yourtable
                                group by serial_num)

Subquery will return the maximum version for serial_num, so this will return all rows where serial_num has the maximum value. See this fiddle.

回答2:

You can use a subquery to find the max values and then join back to your table:

select t1.id,
  t1.serial_num,
  t1.version
from yourtable t1
inner join
(
  select serial_num, 
    max(version) version
  from yourtable
  group by serial_num
) t2
  on t1.serial_num = t2.serial_num
  and t1.version = t2.version

See SQL Fiddle with Demo

Result:

| ID | SERIAL_NUM | VERSION |
-----------------------------
|  1 |          1 |       1 |
|  3 |          2 |       2 |
|  4 |          3 |       1 |
|  5 |          4 |       1 |
|  8 |          5 |       3 |

回答3:

Selecting id for a group by query is useless. You should only select the column you are using in group by and other columns that are being applied aggregate functions.

Hope this works for you.

SELECT id, 
       serial_num, 
       Max(`version`) `version`
FROM   tbl1 
GROUP  BY serial_num