Excube Dzynes Excube Dzynes - 1 month ago 9
MySQL Question

Get last mysql record only from a column

This is my existing table

id name version
| 1 | a | 1.1 |
| 2 | b | 2.1 |
| 3 | c | 3.1 |
| 4 | d | 1.2 |
| 5 | e | 4.1 |


how can I write a query to generate results where i will return all records but only the last record in the column version is selected like below?

id name version
| 4 | d | 1.2 |
| 2 | b | 2.1 |
| 3 | c | 3.1 |
| 5 | e | 4.1 |

Answer

This is a bit laborious but it can be done

SELECT
  SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY REPLACE(grade,'.','')*1 DESC),',',1) as id,
  SUBSTRING_INDEX(GROUP_CONCAT(letter ORDER BY REPLACE(grade,'.','')*1 DESC),',',1) as letter,
  MAX(grade) as grade
FROM
  t
  GROUP BY SUBSTRING_INDEX(grade,'.',1)
  ORDER BY REPLACE(grade,'.','')*1 

Assuming the last column is float you can use ORDER BY lastcol directly

FIDDLE

CREATE TABLE t
    (`id` int, `letter` varchar(7), `grade` varchar(55))
;

INSERT INTO t

VALUES
    (1, 'a', '1.1'),
    (2, 'b', '2.1'),
    (3, 'c', '3.1'),
    (4, 'd', '1.2'),
    (5, 'e', '4.1')
Comments