Hello all,

I have a table that contains the following fields.

ObLocation : String
ObTime : TimeStamp
ObTemp : Integer;
ObWind : Integer;

1. The query must return all fields.
2. The query must return records within a given date/time range.
3. The tricky part for me... The query must only return the latest record based on ObTime (so there's only 1 record per ObLocation...)
There are occasions when 1 ObLocation will have multiple records per hour, but I only want the latest within the time range.
I hope that makes sense...

Thanks for any help you can provide.
Kind regards,
Bryan

Bryan,

"Bryan" <bryanray67@hotmail.com> wrote in message
news:5AA1E100-CAE5-48EE-B5BA-BDC7E722FCD7@news.elevatesoft.com...
> Hello all,
>
> I have a table that contains the following fields.
>
> ObLocation : String
> ObTime : TimeStamp
> ObTemp : Integer;
> ObWind : Integer;
>

<<  1. The query must return all fields.
2. The query must return records within a given date/time range.
3. The tricky part for me... The query must only return the latest record
based on ObTime (so there's only 1 record per ObLocation...) >>

You'll have to use two queries in a script:

SELECT ObLocation, Max(ObTime) AS ObTime
INTO "\Memory\Temp"
FROM MyTable
WHERE ObTime BETWEEN <BeginTimeStamp> AND <EndTimeStamp>
GROUP BY ObLocation;

SELECT T.ObLocation, T.ObTime, M.Obtemp, M.ObWind
FROM "\Memory\Temp" T
INNER JOIN MyTable M ON T.ObLocation = M.ObLocation;

Be sure to drop the in-memory Temp table when you're done, also.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

You'll have to use two queries in a script:

SELECT ObLocation, Max(ObTime) AS ObTime
INTO "\Memory\Temp"
FROM MyTable
WHERE ObTime BETWEEN <BeginTimeStamp> AND <EndTimeStamp>
GROUP BY ObLocation;

SELECT T.ObLocation, T.ObTime, M.Obtemp, M.ObWind
FROM "\Memory\Temp" T
INNER JOIN MyTable M ON T.ObLocation = M.ObLocation;

Be sure to drop the in-memory Temp table when you're done, also.

--
Tim Young
Elevate Software
www.elevatesoft.com


Hi Tim,
The query executed, but I didn't get the results I thought I should...
It seems that the ObTime returned was the same for 2 different records, but in fact, the table shows different times.
I've attached a screen shot that shows the query results and the actual table.  "BIKF" is the record I'm looking at and it shows
differnet WindS values for exact same time, but the table shows the times as 2:00 PM and 2:30 PM.

Am I doing something wrong?  The table does not contain any repeat data where the ICAO and the ObTime are the same.

Here is the SQL I'm using (slightly modified based on my field names and table name....)

SELECT ICAO, Max(ObTime) AS ObTime
INTO "\Memory\Temp"
FROM CurrentObs
WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM'
GROUP BY ICAO;

SELECT T.ICAO, T.ObTime, M.Temp, M.WindS
FROM "\Memory\Temp" T
INNER JOIN CurrentObs M ON T.ICAO = M.ICAO;

Thanks!
Bryan

Bryan,

<< The query executed, but I didn't get the results I thought I should...
It seems that the ObTime returned was the same for 2 different records, but
in fact, the table shows different times.
I've attached a screen shot that shows the query results and the actual
table.  "BIKF" is the record I'm looking at and it shows differnet WindS
values for exact same time, but the table shows the times as 2:00 PM and
2:30 PM.

Am I doing something wrong?  The table does not contain any repeat data
where the ICAO and the ObTime are the same. >>

The issue is that the ICAO field isn't unique, and I was under the
impression it was based upon what you said about there being one record per
location.  Try this instead:

SELECT ICAO, Max(ObTime) AS ObTime
INTO "\Memory\Temp"
FROM CurrentObs
WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM'
GROUP BY ICAO;

SELECT T.ICAO, T.ObTime, M.Temp, M.WindS
FROM "\Memory\Temp" T
INNER JOIN CurrentObs M ON T.ICAO = M.ICAO AND T.ObTime = M.ObTime;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
That did the trick.  Thanks for your outstanding support.
Bryan

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Bryan,

<< The query executed, but I didn't get the results I thought I should...
It seems that the ObTime returned was the same for 2 different records, but
in fact, the table shows different times.
I've attached a screen shot that shows the query results and the actual
table.  "BIKF" is the record I'm looking at and it shows differnet WindS
values for exact same time, but the table shows the times as 2:00 PM and
2:30 PM.

Am I doing something wrong?  The table does not contain any repeat data
where the ICAO and the ObTime are the same. >>

The issue is that the ICAO field isn't unique, and I was under the
impression it was based upon what you said about there being one record per
location.  Try this instead:

SELECT ICAO, Max(ObTime) AS ObTime
INTO "\Memory\Temp"
FROM CurrentObs
WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM'
GROUP BY ICAO;

SELECT T.ICAO, T.ObTime, M.Temp, M.WindS
FROM "\Memory\Temp" T
INNER JOIN CurrentObs M ON T.ICAO = M.ICAO AND T.ObTime = M.ObTime;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
What do you mean by "Be sure to drop the in-memory Temp table when you're done, also."
I'm just using the SQL statement you gave... How do I drop the in-memory temp table?

Bryan

Bryan <bryanray67@hotmail.com> wrote:

Tim,
That did the trick.  Thanks for your outstanding support.
Bryan

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Bryan,

<< The query executed, but I didn't get the results I thought I should...
It seems that the ObTime returned was the same for 2 different records, but
in fact, the table shows different times.
I've attached a screen shot that shows the query results and the actual
table.  "BIKF" is the record I'm looking at and it shows differnet WindS
values for exact same time, but the table shows the times as 2:00 PM and
2:30 PM.

Am I doing something wrong?  The table does not contain any repeat data
where the ICAO and the ObTime are the same. >>

The issue is that the ICAO field isn't unique, and I was under the
impression it was based upon what you said about there being one record per
location.  Try this instead:

SELECT ICAO, Max(ObTime) AS ObTime
INTO "\Memory\Temp"
FROM CurrentObs
WHERE ObTime BETWEEN '2007-05-07 1:00 PM' AND '2007-05-07 2:00 PM'
GROUP BY ICAO;

SELECT T.ICAO, T.ObTime, M.Temp, M.WindS
FROM "\Memory\Temp" T
INNER JOIN CurrentObs M ON T.ICAO = M.ICAO AND T.ObTime = M.ObTime;

--
Tim Young
Elevate Software
www.elevatesoft.com

Bryan,

<< What do you mean by "Be sure to drop the in-memory Temp table when you're
done, also." I'm just using the SQL statement you gave... How do I drop the
in-memory temp table? >>

You have to issue this statement when you're done accessing the result set
(closed it) generated by the script that I gave you:

DROP TABLE "\Memory\Temp"

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
I have a couple questions about dropping the memory table...

1.  Can I control where the memory table is created....?  It seems that there is a table created with a random number number in the name (e.g. 2438.dat and 2438.idx).

2.  I have the following code to remove the temp table:
Procedure TFObservations.DropTempTable;
begin
 MData.q.SQL.Clear;
 MData.q.SQL.Add('DROP TABLE "Memory\Temp";');
 Try
   MData.q.Prepare;
   MData.q.ExecSQL;
 Except

 End;
end;

It seems to work and removes the temp table (numeric table name), but if I set breakpoints, I can see that the table is actually "removed" from the folder when the first line
is executed "MData.q.SQL.Clear".  I would think that the table would be removed/deleted when the MData.q.ExecSQL line was executed.

Any thoughts...?  Am I doing this correctly?

Thanks!
Bryan


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Bryan,

<< What do you mean by "Be sure to drop the in-memory Temp table when you're
done, also." I'm just using the SQL statement you gave... How do I drop the
in-memory temp table? >>

You have to issue this statement when you're done accessing the result set
(closed it) generated by the script that I gave you:

DROP TABLE "\Memory\Temp"

--
Tim Young
Elevate Software
www.elevatesoft.com

"Bryan" <bryan@extremewx.com> wrote in message
news:F3CAC7C9-4152-47E5-B555-B23488188E13@news.elevatesoft.com...
> Tim,
> I have a couple questions about dropping the memory table...
>
> 1.  Can I control where the memory table is created....?  It seems that
> there is a table created with a random number number in the name (e.g.
> 2438.dat and 2438.idx).
>
> 2.  I have the following code to remove the temp table:
> Procedure TFObservations.DropTempTable;
> begin
>  MData.q.SQL.Clear;
>  MData.q.SQL.Add('DROP TABLE "Memory\Temp";');
>  Try
>    MData.q.Prepare;
>    MData.q.ExecSQL;
>  Except
>
>  End;
> end;
>
> It seems to work and removes the temp table (numeric table name), but if I
> set breakpoints, I can see that the table is actually "removed" from the
> folder when the first line
> is executed "MData.q.SQL.Clear".  I would think that the table would be
> removed/deleted when the MData.q.ExecSQL line was executed.
>

That table is a memory table, it is not in any folder. You are looking at a
different table. A memory table resides in memory, not on disk.

Robert

> Any thoughts...?  Am I doing this correctly?
>
> Thanks!
> Bryan
>
>
> "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
>
> Bryan,
>
> << What do you mean by "Be sure to drop the in-memory Temp table when
> you're
> done, also." I'm just using the SQL statement you gave... How do I drop
> the
> in-memory temp table? >>
>
> You have to issue this statement when you're done accessing the result set
> (closed it) generated by the script that I gave you:
>
> DROP TABLE "\Memory\Temp"
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Bryan


Just to expand on what Robert said.

The numeric named tables are DBISAM's temporary tables created when you run a query resulting in a canned dataset. These are cleaned up whenever the query is closed. If you find them on your disk after closing an app (or the IDE) its because the system didn't shut down correctly.

You are doing it correctly to remove the memory table, however, I'd ditch the .Prepare. In these circumstances I don't think its doing much and you certainly don't want to run the sql again to remove the memory table when its already gone

I've put my alternative function below.


Roy Lambert

procedure ZapInMemoryTable(WhichTable: TDBISAMTable);
begin
try
 if Assigned(WhichTable) and WhichTable.Exists then begin
  WhichTable.DisableControls;
  WhichTable.Close;
  WhichTable.Exclusive := True;
  if not (WhichTable.Bof and WhichTable.Eof) then WhichTable.EmptyTable;
  WhichTable.DeleteTable;
  FreeAndNil(WhichTable);
 end;
except
end;
end;