Get latest record in MySQL

Question

I have this table with employee contract records.

+----+---------------------------+
| id | start| end     | employee |
+----+---------------------------+
|  8 | 2016 | 2017    | 777      |
|  7 | 2014 | 2015    | 777      |
|  6 | 2012 | 2013    | 777      |
|  5 | 2010 | 2011    | 777      |
|  3 | 2016 | 2017    | 666      |
|  4 | 2014 | 2015    | 666      |
|  2 | 2012 | 2013    | 666      |
|  1 | 2010 | 2011    | 666      |
+----+---------------------------+

I'm having difficulty getting the latest contract per employee.

The query should print:

+----+-----------------+
| id | start| employee |
+----+-----------------+
|  8 | 2016 | 777      |
|  3 | 2016 | 666      |
+----+-----------------+

I have tried so far:

SELECT 
MAX(start)
,id
,employee
FROM contract
GROUP BY employee

but that gives me id that doesn't correspond to the record.

This is a common question for mysql and group by http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Jan Zeiseweis, Jan 20 '17 at 12:06

score 1 · Answer 1 · answered Jan 20 '17 at 12:05

Don't use group by for this. You want to filter the rows, so use where -- in this case with a subquery of some sort.

Here is one method:

SELECT c.*
FROM contract c
WHERE c.id (SELECT MAX(c2.id)
            FROM contract c2
            WHERE c2.employee = c.employee
           );

score 1 · Answer 2 · answered Jan 20 '17 at 12:13

1

Here is a proposal:

SELECT t.*
FROM test t
WHERE t.start in (SELECT MAX(t2.start)
            FROM test t2
            group by t2.employee
           );

and SQLFiddle LINK

answered Jan 20 '17 at 12:13

starko

1,150
11
26

Raymond Nijland · Answer 3 · 2017-12-07T14:12:04.397

CREATE table/insert data

CREATE TABLE contract
    (`id` INT, `start` INT, `end` INT, `employee` INT)
;

INSERT INTO contract
    (`id`, `start`, `end`, `employee`)
VALUES
    (8, 2016, 2017, 777),
    (7, 2014, 2015, 777),
    (6, 2012, 2013, 777),
    (5, 2010, 2011, 777),
    (3, 2016, 2017, 666),
    (4, 2014, 2015, 666),
    (2, 2012, 2013, 666),
    (1, 2010, 2011, 666)
;

Use user variables to mark the greatest price off every grouped employee.

User variables query

SELECT
     contract.id
   , contract.start
   , contract.employee
   , (contract.employee != @employee) AS employeeMax -- Mark a new employee as Max
   , (@employee := contract.employee) AS employeeInit
  FROM
   contract
  ORDER BY
      contract.employee 
    , contract.start DESC -- DESC for MAX(start) ASC for MIN(start)

Result

    id   start  employee  employeeMax  employeeInit  
------  ------  --------  -----------  --------------
     3    2016       666            1             666
     4    2014       666            0             666
     2    2012       666            0             666
     1    2010       666            0             666
     8    2016       777            1             777
     7    2014       777            0             777
     6    2012       777            0             777
     5    2010       777            0             777

Complete Query

Now filter on employeeMax = 1 to only keep the greatest price records like so.

SELECT
   contract_group.id
 , contract_group.start
 , contract_group.employee
FROM (
  SELECT
     contract.id
   , contract.start
   , contract.employee
   , (contract.employee != @employee) AS employeeMax -- Mark a new employee as Max
   , (@employee := contract.employee) AS employeeInit
  FROM
   contract
  ORDER BY
      contract.employee 
    , contract.start DESC -- DESC for MAX(start) ASC for MIN(start)
)
 AS
   contract_group
WHERE
 contract_group.employeeMax = 1 -- keep only the employee max marked records

Result

    id   start  employee  
------  ------  ----------
     3    2016         666
     8    2016         777

Get latest record in MySQL

3 Answers3