SQL - TOP 1 Usage

E

ell

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
 
J

Jake Marx

Hi ell,

A subquery may work in this case:

SELECT SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc,
(SELECT TOP 1 SLH.slh_date
FROM root.SLH SLH
WHERE SLH.slh_cref=SLT.slt_cref
ORDER BY SLH.slh_date DESC)
FROM root.SLT SLT
ORDER BY SLT.slt_cref

Without having the database structure, I can only assume this will work - it
looks like it should. This query should return the cref, type, fcval,
alloc, and slh_date for each cref in your SLT table.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
E

ell

Hi Jake,

Thank you for the feedback.

I have tried the query you kindly submitted, but my SQL software i
displaying the following error:

*** Error: ')' expected here (TOP)

Assuming it's expecting another closed bracket, I can't figure ou
where.

The structure for my two tables of interest are as follows:

SLT
fldname type length offset
slt_cref A 6 0
slt_date D 4 6
slt_ref A 10 10
slt_type A 5 20
slt_ref2 A 20 25
slt_oldind A 1 45
slt_am1 M 4 46
slt_am2 M 4 50
slt_alloc M 4 54
slt_fwdalloc M 4 58
slt_duedate D 4 62
slt_disc M 4 66
slt_setdate D 4 70
slt_setdisc M 4 74
slt_cost M 4 78
slt_perno I 1 82
slt_exch R 8 83
slt_fcval R 8 91
slt_fcdisc R 8 99
slt_fcall R 8 107
slt_fcfwdall R 8 115
slt_perenddate D 4 123
slt_yrenddate D 4 127
slt_entdate D 4 131
slt_time M 4 135
slt_loginid A 8 139
slt_finperno I 1 147
slt_batchno I 2 148
slt_batchtrno I 2 150
slt_filler A 28 152

SLH
fldname type length offset
slh_cref A 6 0
slh_date D 4 6
slh_am2 M 4 49
slh_duedate D 4 53
slh_disc M 4 57
slh_setdate D 4 61
slh_setdisc M 4 65
slh_cost M 4 69
slh_exch R 8 73
slh_fcval R 8 81
slh_fcdisc R 8 89
slh_perenddate D 4 97
slh_yrenddate D 4 101
slh_entdate D 4 105
slh_time M 4 109
slh_loginid A 8 113
slh_finperno I 1 121
slh_batchno I 2 122
slh_batchtrno I 2 124
slh_age I 1 126
slh_filler A 1 127

These are indexed fields:
slh_ref, slh_cref, slh_date
slt_ref, slt_cref, slh_date

But the two tables can only be linked via the *_cref field.

Is that what you implied by structure?


Thank you once again for any assistance.

Elliot

Jake said:
*Hi ell,

A subquery may work in this case:

SELECT SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc,
(SELECT TOP 1 SLH.slh_date
FROM root.SLH SLH
WHERE SLH.slh_cref=SLT.slt_cref
ORDER BY SLH.slh_date DESC)
FROM root.SLT SLT
ORDER BY SLT.slt_cref

Without having the database structure, I can only assume this wil
work - it
looks like it should. This query should return the cref, type
fcval,
alloc, and slh_date for each cref in your SLT table.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

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 I
require is the most recent date against the cref criteria.

The query is to show customer debts (SLT) over 90 days old, the their
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_cre =
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
returned for each cref returned from SLT. This is fine.

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

I tried:

SELECT SLT.slt_cref, SLT.slt_type, SLT.slt_fcval, SLT.slt_alloc TOP 1
slh.slh_date
FROM {oj root.SLT SLT LEFT OUTER JOIN root.SLH SLH ON SLT.slt_cre =
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 column
list'.

All suggestions and comments gratefully received.

Elliot
MS Query via Excel 2000
Transoft USQL Middleware on Linux RedHat Sculptor 4GL Database
Click to expand...
Click to expand...
 
J

Jake Marx

Hi ell,
I have tried the query you kindly submitted, but my SQL software is
displaying the following error:

*** Error: ')' expected here (TOP)
Click to expand...

I think this may be the stumbling block:
Transoft USQL Middleware on Linux RedHat Sculptor 4GL Database
Click to expand...

I tried a similar query hitting SQL Server via MS Query (XL 2002), and it
worked fine even though MS Query couldn't represent the query graphically.
So maybe the middleware or the DB itself doesn't like the syntax. You could
try adding/removing parenthesis to see if you can get it to work, but I
don't have either of those tools, so I can't test it any further. Good
luck!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
E

ell

Jake said:
*Hi ell,
I have tried the query you kindly submitted, but my SQL software is
displaying the following error:

*** Error: ')' expected here (TOP)
Click to expand...

I think this may be the stumbling block:
Transoft USQL Middleware on Linux RedHat Sculptor 4GL Database
Click to expand...

I tried a similar query hitting SQL Server via MS Query (XL 2002),
and it
worked fine even though MS Query couldn't represent the query
graphically.
So maybe the middleware or the DB itself doesn't like the syntax.
You could
try adding/removing parenthesis to see if you can get it to work, but
I
don't have either of those tools, so I can't test it any further.
Good
luck!

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored] *
Click to expand...

Thanks again for the reply, Jake.

I suspected that there were some syntax conflicts at play.

I have contacted the supplier of our SQL driver, and Transoft directly,
for some help regarding syntax structure. Hopefully I should hear from
them soon.

Ell
 
J

Jake Marx

Jamie said:
See various posts:

http://groups.google.com/groups?q="top+n"+proprietary+celko

e.g. "SELECT TOP (n)is a non-relational, strictly (MS) proprietary
"feature"... (use) a subquery to establish a subset based on a count."
Click to expand...


Looks like at least some version(s) of U/SQL support the "TOP n" feature,
however:

http://www.transoft.com/support/RelNotes/usql/USQL320/NewFeaturesUSQL310407.htm

But I would guess you're probably correct if the OP can't get 2 different
TOP n queries to work.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Top