get rows from a table where value of field x is maximum

Question

I have two tables myTable and myTable2 in a mysql database:

CREATE TABLE myTable (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    number INT,
    version INT, 
    date DATE 
) ENGINE MyISAM;

INSERT INTO myTable
    (`id`, `number`, `version`, `date`)
VALUES
    (1, '123', '1', '2016-01-12'),
    (2, '123', '2', '2016-01-13'),
    (3, '124', '1', '2016-01-14'),
    (4, '124', '2', '2016-01-15'),
    (5, '124', '3', '2016-01-16'),
    (6, '125', '1', '2016-01-17')
;

CREATE TABLE myTable2 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    myTable_id INT
) ENGINE MyISAM;

INSERT INTO myTable2
    (`id`, `myTable_id`)
VALUES
    (1, 1),
    (2, 1),
    (3, 2),
    (4, 2),
    (5, 3),
    (6, 3),
    (7, 4),
    (8, 4),
    (9, 4),
    (10, 5),
    (11, 6)
;

The field myTable2.myTable_id is a foreign key of myTable.Id.

I would like to get all the rows from myTable where myTable2.myTable_id = myTable.Id and the value of the field version in myTable is the maximum for every corresponding value for the field number in myTable.

I tried something like this:

SELECT 
    * 
FROM 
    myTable, 
    myTable2 
WHERE 
    myTable.version = (SELECT MAX(myTable.version) FROM myTable)

But the above query does not return the correct data. The correct query should output this:

Id     number    version    date
2      123       2          2016-01-13
5      124       3          2016-01-16
6      125       1          2016-01-17

Please help!

Answer 1

One way to do this is to get the max version for each number in myTable in a derived table and join with that:

SELECT DISTINCT 
    m.* 
FROM 
    myTable m
JOIN
    myTable2 m2 ON m.id = m2.myTable_id
JOIN
    (
    SELECT number, MAX(version) AS max_version 
    FROM myTable
    GROUP BY number
    ) AS derived_table
 ON m.number  = derived_table.number 
AND m.version = derived_table.max_version

With your sample data this produces a result like this:

id  number  version date
6   125     1       2016-01-17
5   124     3       2016-01-16
2   123     2       2016-01-13

Answer 2

your Query is logically wrong. Here is the correct one

SELECT 
 * 
FROM 
 myTable, 
 myTable2 
WHERE 
 (myTable.version,myTable.number) in 
  (SELECT MAX(myTable.version),number FROM myTable group by number)
 and myTable.id=myTable2.id

Here is the sqlfiddle http://sqlfiddle.com/#!9/74a67/4/0