(I'm moving this thread to the database list then - this is the only cross
post I promise!)
Yes Carl that one works but like my original one
SELECT * FROM hardone F
WHERE F.GMT_EVENT =
(SELECT MAX(W.GMT_EVENT)
FROM hardone W
WHERE W.ITEM = F.ITEM)
it's doing a 'select' for every row that it looks at - which means for "n"
rows in the hardone table it's going to do roughly n^2 operations.
The kludge I used (see below database@delphi people) combines the two
columns into 1 and gets the answer in one pass (order n). Speed is very
important for this particular query. I just want to know if there is a more
formal way of doing it..
thanks
mike
.... How many people are on the database list and not the delphi list?
> 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
THanks
Mike
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of Carl Reynolds
> Sent: Friday, 1 September 2000 09:41
> To: Multiple recipients of list delphi
> Subject: RE: [DUG]: Question on style for you SQL Gurus
>
>
> Try this one for size:
>
> select *
> from hardone h1
> where not exists (
> select 1
> from hardone h2
> where h2.gmt_event > h1.gmt_event)
>
> BTW, there is a database list for Delphi database/SQL specific questions:
> [EMAIL PROTECTED]
> Mail listserv with "subscribe database" in the body.
>
> Cheers,
> Carl
> ------------------------------------------------------------------
> ---------
> 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"
*** This is the original post for the benefit of [EMAIL PROTECTED]
subscribers that are not on [EMAIL PROTECTED] ***
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"
