Max Date statement on Where clause

[#1]

Hi,

In Qv load script, how can we write the following statement with the clause to get the Max Date like the example below:

SELECT *

FROM Table1  INNER JOIN Table2  ON  Table1.id = Table2.Id

WHERE Table1.Date  = (select max(T1.Date)

                                               from Table1 T1

                                           where Table1.id = T1.id)

Someone could advice a way to achieve this?

Many thanks

Paulo

[maheshkuttappa]

try this

SELECT *

FROM Table1 A ,

Table2 B

(select max(T1.Date) from

Table1 T1) C

Where A.id=B.Id

And A.id=C.id 

[#3]

Table1:

load max(Date) as Date,

          id

from ... group by id;

//table2

inner join (Table1)

load *

from ...

[sunny_talwar]

May be this:

Table:

LOAD Id,

          Date

From ....

Inner Join (Table)

LOAD Id,

     Max(Date) as Date

Resident Table

Group By Id;

[vishsaggi]

Kind of a work around, let me know if this works ?

Table1:

SELECT *

FROM Table1  INNER JOIN Table2  ON  Table1.id = Table2.Id;

Temp:

LOAD max(Date1) AS MaxDate

Resident Table1;

LET vMaxDate = Date(Peek('MaxDate',0, 'Temp'), 'MM/DD/YYYY');

Drop Table Temp;

Final:

Load *

Resident Table1

Where Date = $(vMaxDate);