(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 - Database List - [EMAIL PROTECTED] Website: http://www.delphi.org.nz
