Does 'I'm using the BDE "Local SQL" ....' mean Paradox.
If it does; you can do "Views" in Pdx by referring to a text file that has
an SQL statement in it. Off the top of my head - how about a view that goes:
select levelid, max(gmt_event) from hardone group by levelid
in "hardoneview.sql"
and
select * from hardone h inner join hardoneview v on h.levelid = v.levelid
and h.gmt_event = v.gmt_event order by h.levelid, h.gmt_event
I haven't even thought about testing it so no promises.
Max
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Mike Mueller
Sent: Friday, 1 September 2000 08:56
To: Multiple recipients of list delphi
Subject: [DUG]: Question on style for you SQL Gurus
I have an SQL query that I'm trying to get to work as fast as possible. My
initial version worked fine, but was doggedly slow because it had a 'select'
as one of the 'where' conditions. (which makes it an n-squared operation I
believe). I managed to get it to work a million times faster (ie. order 'n'
only now) but it was a little bit of a kludge and I'm wondering if there was
a more elegant way to do it. I won't be suprised if no-one answers because
you've all got your own problems but hey it's worth a try anyway (: I think
it's a curious one
I'm simplifying the problem to 3 columns, ITEM, GMT_EVENT and LEVEL. I'll
call the table HARDONE. For every distinct 'ITEM' the LEVEL is measured on
any number of GMT_EVENTS. I want to get the latest 'LEVEL' value for every
ITEM. The GMT_EVENT is in a 29 character ASCII format such that
alphabetical order = chronological order (ie. YYYY-MM-DD
HH-MM-SEC:NANOSECONDS). The LEVEL is a number from 0 to 4. ITEM and
GMT_EVENT form the primary key.
So this does it nice and slow:
SELECT * FROM hardone F
WHERE F.GMT_EVENT =
(SELECT MAX(W.GMT_EVENT)
FROM hardone W
WHERE W.ITEM = F.ITEM)
I thought I could get it to do it in one pass with a 'GROUP BY' and a JOIN
but no luck with that. I had to include the LEVEL in the GROUP BY because
it's in the "projection list" !??! to get it to run which means I'm getting
the latest GMT_EVENT for every LEVEL for every ITEM. (not quite right) It
also looks like the JOIN is applied *before* the GROUP BY only. (If you
know how to get the JOIN to happen after the GROUP BY then let me know!)
The fast way that I found of doing it was
SELECT ITEM,
MAX(GMT_EVENT || CAST(LEVEL_ID AS VARCHAR(1)))
FROM hardone
GROUP BY ITEM
Where I join the GMT_EVENT and LEVEL fields into one 30 character field and
find the max value for every ITEM. (Because I am garaunteed that there can
never be two measurements of LEVEL at the same time for an item). In the
result set I use an "OnCalcFields" to pull out the original GMT_EVENT and
LEVEL_ID values. (Sorry I changed to LEVEL_ID because it seemed to think
that "LEVEL" was a keyword!?!?)
So you can see that it's a little bit of a kludge... Is there a better way
of doing it?
BTW I'm using the BDE "Local SQL" ....
Free beer for the first person who kindly points out a better way of doing
it
Mike
PS. did anyone hear about the new christchurch based software association
(evolved out of nzsa)?
Here are the fields in the test table 'hardone'
ITEM INTEGER PRIMARY KEY
GMT_EVENT VARCHAR(29) PRIMARY KEY
LEVEL INTEGER
Here are the test values for hardone that I used
ITEM GMT_EVENT LEVEL
1 2000-07-01 3
1 2000-08-01 2
1 2000-09-01 1
2 2000-07-01 1
2 2000-08-01 2
2 2000-09-01 3
The result set that I want is
ITEM GMT_EVENT LEVEL
1 2000-09-01 1
2 2000-09-01 3
Using the kludge it looks like
ITEM GMT_EVENT_LEVEL
1 2000-09-011
2 2000-09-013
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
