CODEWITHSUNDEEP
mysqlselectuniquemax
M.V.
M.V.

Reputation: 1672

MySQL SELECT unique column where other column is max

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!

Upvotes: 5

Views: 12502

Answers (3)

Taryn
Taryn

Reputation: 247670

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 |

Upvotes: 3

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

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

Upvotes: -1

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 12

Related Questions