Hi All,

I'm having difficulty getting the TOP 1 statement to work.

I have two tables, SLT and SLH. I am linking them, by field 'cref
(primary key). SLH has multiple instances of cref matches; all
require is the most recent date against the cref criteria.

The query is to show customer debts (SLT) over 90 days old, then thei
last payment date (from SLH).

For example, I can get this to do what I want:

SELECT SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc
Max(SLH.slh_date) AS 'Max of slh_date'
FROM {oj root.SLT SLT LEFT OUTER JOIN root.SLH SLH ON SLT.slt_cref
SLH.slh_cref}
WHERE (SLT.slt_date<={d '2004-04-30'})
GROUP BY SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc

The most recent date in SLH, found against each cref match, is returne
for each cref returned from SLT. This is fine.

However, the query takes a long time to run. So, I have read that th
TOP 1 statement could be used instead. However, I am unsure how to us
it. Could somebody please help?

I tried:

SELECT SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc, TOP
slh.slh_date
FROM {oj root.SLT SLT LEFT OUTER JOIN root.SLH SLH ON SLT.slt_cref
SLH.slh_cref}
WHERE (SLT.slt_date<={d '2004-04-30'})
GROUP BY SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc
ORDER BY slh.slh_cref, slh.slh_date DESC

However, I receive, 'Didn't expect 'slh' after the SELECT colum
list'.

All suggestions and comments gratefully received.

Elliot
MS Query via Excel 2000
Transoft USQL Middleware on Linux RedHat Sculptor 4GL Databas

--
Message posted from http://www.ExcelForum.com