Hi,

can anyone help me with this query? I have a table that collects date, route and time of
running. Now I simply want to get the record of each route, that means the smallest time.

Normally I would write (Strecke = route, Zeit = Time):

select Datum, Strecke, min(Zeit) as Record from Laufen group by Strecke;

But in this case I con't get the correct date that matches the record. So I use a
TEMPORARY TABLE in order to be able to locate the correct date and edit it in the table
afterwards:

CREATE TEMPORARY TABLE tmp_Record AS select *, min(zeit) as Record from Laufen
group by Strecke WITH DATA;

But that seems to complicated. Is there a possibility to get the desired data in ONE query?

Thanks. Felix

Felix,

<< can anyone help me with this query? I have a table that collects date,
route and time of running. Now I simply want to get the record of each
route, that means the smallest time.

Normally I would write (Strecke = route, Zeit = Time):

select Datum, Strecke, min(Zeit) as Record from Laufen group by Strecke; >>

Why are you including the Datum column ?

Normally, the query should look like this:

select Strecke, min(Zeit) as Record from Laufen group by Strecke

Does this query have the same issue ?

--
Tim Young
Elevate Software
www.elevatesoft.com

<<Why are you including the Datum column ?>>

I include the Date column because I want to see when the record was run. The result should
look like this:

2005-05-12 | Marathon         | 3:20:15
2006-07-22 | MyRoute 20km | 1:27:22

...

Regards Felix

Felix wrote:

>
> <<Why are you including the Datum column ?>>
>
> I include the Date column because I want to see when the record was
> run. The result should look like this:
>
> 2005-05-12 | Marathon         | 3:20:15
> 2006-07-22 | MyRoute 20km | 1:27:22
>
> ..
>
> Regards Felix

Pretty fine run times, Felix!  I need to borrow your legs for my next
race.

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Tim Young [Elevate Software] wrote:

>
>
> Normally, the query should look like this:
>
> select Strecke, min(Zeit) as Record from Laufen group by Strecke
>

Kia Orana Felix

Maybe use a memory table, something like (DBISAM V3.30 syntax):-
DROP TABLE IF EXISTS T
;
SELECT Strecke, min(Zeit) AS Record
INTO MEMORY T
FROM Laufen GROUP BY Strecke
;
ALTER TABLE MEMORY
ADD RecordDate DATE
;
UPDATE MEMORY T
SET T.RecordDate = L.Datum
FROM MEMORY T
JOIN Laufen L ON (L.Ziet = Record)
;
SELECT * FROM MEMORY T
;

Of course completely untested ...

Kia Manuia

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

@Jeff: You are from the Cook Islands? (I just googled that.) Great! Good luck for your
next runs

Thanks for your help - I agree with you. It is not possible to do this in ONE Query.

Regards Felix

Felix,

<< I include the Date column because I want to see when the record was run.
The result should look like this:

2005-05-12 | Marathon         | 3:20:15
2006-07-22 | MyRoute 20km | 1:27:22 >>

In that case you should group on both the date and the route to ensure the
correct results.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< In that case you should group on both the date and the route to ensure the
correct results. >>

You mean: select Datum, Strecke, min(Zeit) as Record from Laufen group by Strecke, Datum; ?

That doesn't work. I don't know whether I could make clear the issue.

An example: from this table

2005-05-12 | Marathon         | 3:20:15
2006-08-10 | Marathon         | 3:24:20
2007-10-25 | Marathon         | 3:40:01
2006-07-22 | MyRoute 20km | 1:27:22
2007-06-10 | MyRoute 20km | 1:32:10
2004-07-25 | MyRoute 20km | 1:26:56

I want to get this result:

2005-05-12 | Marathon         | 3:20:15
2004-07-25 | MyRoute 20km | 1:26:56

No problem to do a query and edit it afterwards, but the question is, whether or not it is
possible in ONE Query.

Regards Felix

Felix wrote:

> @Jeff: You are from the Cook Islands? (I just googled that.) Great!
> Good luck for your next runs
>
>
Felix

If you are ever down this way, time your visit for the annual Round
Rarotonga Road Race - 31km.  RRRR is usually in October and is part of
a week of running events with fun runs, relays and the infamous
"Nutters Run" across the island the hilly way.

Cheers

Jeff

P.S.  If you are interested in triathlon then go here
www.cookislandsoasis.com/rates.html#Triathlon . J.

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Felix,

Does this do what you want?

select a.Strecke, a.Datum, a.Zeit from Laufen a
where a.Zeit =
(select min(Zeit) from Laufen b where b.Strecke=a.Strecke)



Felix wrote:
> @Jeff: You are from the Cook Islands? (I just googled that.) Great! Good luck for your
> next runs
>
> Thanks for your help - I agree with you. It is not possible to do this in ONE Query.
>
> Regards Felix
>
>