Mysql query - need to get the latest result set when compared with two tables

[seenu_vas80]

HI,

 

  Iam facing a problem while comparing two tables to get the latest result from the table 2. I have mentioned the problem in detail below.

 

Table1

Id Name ver_no Flag Attempt

1 Lorem Ipsum EN001 1 6

2 Lorem Ipsum EN002 2 5

 

 

Table2

Id Text_val Ver_no Status Doa

1 Message1 EN001 A 2009-05-26

2 Message 2 EN001 A 2009-05-26

3 Message 3 EN001 A 2009-05-27

4 Message 4 EN002 A 2009-05-27

5 Message 5 EN001 A 2009-05-27

6 Message 6 EN002 A 2009-05-27

 

By comparing these two tables, we need to get the latest record in the table2 for each and every version. We have tried by using “Group by” clause of the field ‘ver_no’ which is common to both the tables. While doing this we are getting the record which has been inserted at the beginning of each ver_no and not the latest one. 

 

While running the query we need the result row as:

Id Text_val Ver_no doa

5 Message 5 EN001 2009-05-27

6 Message 6 EN002 2009-05-27

 

 

I have listed the query which we have tried below:

 

Select table2.id, table2. Text_val, table2.ver_no, table2.doa from table1, table2 where table1.ver_no = table2.ver_no group by table2.ver_no

 

These query returns the first entry of the version in the table2 as a result. But I need the latest record that has been inserted in the table2 as the result.

 

I have tried with the table operations also. but i couldn't get the result which i expect.

 

Need help on this.

 

 

 

[gassaz]

Try this..

 

 

SELECT

    table2.text_val

    , table1.ver_no

    , max(table2.doa)

FROM

    table1

    INNER JOIN table2

        ON (table1.ver_no = table2.ver_no)

GROUP BY table1.ver_no;

 

[seenu_vas80]

Thanks for reply.

I have tried this.But I'm getting the following result.

 

While running the query we need the result row as:

Id  Text_val  Ver_no  doa

1  Message 5  EN001  2009-05-26

4  Message 6  EN002  2009-05-27

 

Need help on this..

[gassaz]

Sorry, i can't folllow you.....

[Ken2k7]

SELECT t2.id, t2.Text_val, t2.ver_no, t2.doa
FROM table2 t2
INNER JOIN table1 t1
     (ON t2.ver_no = t1.ver_no)
GROUP BY t2.id
ORDER BY t1.id

 

?

[kickstart]

Hi

 

Think what you want is the table1 record matched with the latest record from table2 for that ver_no.

 

As such:-

 

SELECT * 
FROM Table1
JOIN (SELECT Ver_no, Max( Doa ) AS Doa FROM Table2 GROUP BY Ver_no) Deriv1 
ON Table1.Ver_no = Deriv1.Ver_no
JOIN Table2 
ON Deriv1.Ver_no = Table2.Ver_no
AND Deriv1.Doa = Table2.Doa

 

All the best

 

Keith