How to select the desired result in MySQL?

Question

setup:

create table main(id integer unsigned);    
create table test(id integer unsigned,created datetime,text text);

insert into main value(1);
insert into test value(1,now(),'something1');    
insert into test value(1,now() + interval 1 day,'something2');

Using:

select main.id, text from main left join test on main.id=test.id
group by main.id where main.id in (1,2,3);

returns:

+------+------------+
| id   | text       |
+------+------------+
|    1 | something1 |
+------+------------+

How to get

+------+------------+
| id   | text       |
+------+------------+
|    1 | something2 |
+------+------------+

Answer 1

select id, text from main left join test on main.id=test.id order by created

You may need to use ASC(ending) or DESC(ending)

Answer 2

EDITED:

What about this?

select
    main.id, a1.created,a1.text
from
    main
inner join test a1 on
    a1.id = main.id
LEFT OUTER JOIN test a2 ON
    (a1.id = a2.id
    AND (a1.created < a2.created))
WHERE a2.id IS NULL;

Answer 3

Try the following SQL statement:

SELECT id, (
    SELECT text
    FROM test
    WHERE test.ID = main.ID
    ORDER BY created DESC 
    LIMIT 0 , 1
    ) AS text

FROM main

Edit:

In case you need several columns from table test, you need to add a primary key. Then, in your query, first obtain the primary key column and use it as reference on the following sub queries:

# Add the primary key
ALTER TABLE `test` ADD `test_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

# Select many columns
SELECT id, (

    SELECT test_id
    FROM test
    WHERE test.ID = main.ID
    ORDER BY created DESC 
    LIMIT 0 , 1

) AS testID, (

    SELECT text
    FROM test
    WHERE test_id = testID

) AS text, (

    SELECT created
    FROM test
    WHERE test_id = testID

) AS created

FROM main

Answer 4

SELECT 
     main.id , test.created , test.text 
    FROM 
     main 
     JOIN test ON (main.id = test.id) 
     JOIN (select id,max(created) as created from test group by id) as t2 ON (test.id = t2.id and test.created =t2.created)