selecting the most recent record

[JeroenBoon]

Hello,

I have a table with per track and trace number one or more records. Each record has a date and time. Now I want to select the most recent record per track and trace number. To do this, I use the following statement:

SELECT TABLE1.TRACKTRACE,
TABLE1.CODE,
TABLE2.DESCRIPTION,
TABLE1.DATE,
FROM TABLESPACE.TABLE1 TABLE1,
TABLESPACE.TABLE2 TABLE2
WHERE ( TABLE1.CODE = TABLE2.CODE)
AND TABLE1.DATE||TABLE1.TIME in (SELECT MAX(TABLE1_1.DATE||TABLE1_1.TIME)
FROM TABLESPACE.TABLE1 TABLE1_1
WHERE ( TABLE1.TRACKTRACE=TABLE1_1.TRACKTRACE))

But the performance of this view is not good enough. Is there an other way to do this?

Thanks,

Jeroen.

 

[bborissov]

SELECT TABLE1.TRACKTRACE,
       TABLE1.CODE,
       TABLE2.DESCRIPTION,
       TABLE1.DATE,
FROM   TABLESPACE.TABLE1 TABLE1
INNER JOIN
      TABLESPACE.TABLE2 TABLE2 ON TABLE1.CODE = TABLE2.CODE
INNER JOIN (SELECT MAX(DATE||TIME) Test
                   FROM TABLESPACE.TABLE1
                   GROUP BY TRACKTRACE) Tbl1
ON TABLE1.TRACKTRACE = Tbl1.TRACKTRACE AND
   TABLE1.DATE||TABLE1.TIME = Tbl1.Test

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP