db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dmitri Pissarenko" <dmitri.pissare...@gmail.com>
Subject Re: Views in Derby
Date Thu, 28 Jun 2007 12:15:58 GMT
Hello!

Thanks for your hint!

The idea with GROUP BY is really good.

I tried to use it, but now have following problem.

I have these data:

Datetime            | Location  | SomeDataAsString
--------------------+-----------+-----------------
14.05.2007 10:58:55 | LOC_01    | A, B
14.05.2007 11:08:33 | LOC_01    | A, B
14.05.2007 11:14:42 | LOC_01    | A, B
14.05.2007 11:16:11 | LOC_01    | A, B
14.05.2007 11:32:31 | LOC_01    | A, B
14.05.2007 14:20:03 | LOC_01    | A, B
14.05.2007 17:24:06 | LOC_01    | A, B
14.05.2007 17:40:38 | LOC_01    | A, B
15.05.2007 17:26:44 | LOC_01    | B
15.05.2007 17:26:47 | LOC_01    | A, B

and I want to display them so:

Starttime           | Endtime             | Location | SomeDataAsString
--------------------+---------------------+----------+-----------------
14.05.2007 10:58:55 | 15.05.2007 17:26:44 | LOC_01   | A, B
15.05.2007 17:26:44 | 15.05.2007 17:26:47 | LOC_01   | B
15.05.2007 17:26:47 | 15.05.2007 17:26:47 | LOC_01   | A, B

That is, from 14.05.2007 10:58:55 to 14.05.2007 17:40:38 the field
SomeDataAsString was equal to "A, B" and for this time period only one
row should be generated.

At 15.05.2007 17:26:44 the value of SomeDataAsString changes - a new
row is needed.

Finally, at 15.05.2007 17:26:47 SomeDataAsString changes back to "A,
B" - that's the third row.

To make this I wrote following SQL query:

SELECT MIN(Datetime) AS STARTTIME, MAX(Datetime) AS ENDTIME, Location,
SomeDataAsString
FROM APP.MyTable
GROUP BY Location, SomeDataAsString
ORDER BY Location, STARTTIME

It returns following result, which is wrong:

Starttime           | Endtime             | Location | SomeDataAsString
--------------------+---------------------+----------+-----------------
14.05.2007 10:58:55 | 15.05.2007 17:26:47 | LOC_01   | A, B
15.05.2007 17:26:44 | 15.05.2007 17:26:44 | LOC_01   | B

How can I fix this error?

TIA

Dmitri Pissarenko

Mime
View raw message