> SELECT DISTINCT ON (idbucket) t3.bucket AS idbucket, t3.activitycount AS
> activitycount, t3.windowstart AS starttime, t3.windowend AS endtime FROM
> (...) t3
Do you have primary key in your t3 table?
> In Postgresql, what this does is to return the FIRST entire row matching each
> distinct idbucket result.
FIRST based on which sort?
Lets say you want to return FIRST row based on t3.windowstart column
and you have primary key in t3 table. Then I believe your query can be
rewritten in the following ways:
1. Using subqueries
SELECT
bucket, primary_key, windowstart, etc
FROM
table AS t1
WHERE
windowstart=( SELECT max(windowstart) FROM table AS t2 WHERE
bucket = t1.bucket )
2. Using joins instead of subqueries ( in case Derby doesn't support
subqueries - not sure about that )
SELECT
t1.bucket, t1.primary_key, windowstart, etc
FROM
table AS t1
LEFT OUTER JOIN table AS t2 ON ( t1.bucket = t2.bucket AND
t2.windowstart > t1.windowstart )
WHERE
t2.primary_key IS NULL
HTH,
Alex
On Sat, Sep 18, 2010 at 2:28 PM, Karl Wright <daddy...@gmail.com> wrote:
> Hi Folks,
>
> For two of the report queries, ACF uses the following Postgresql
> construct, which sadly seems to have no Derby equivalent:
>
> SELECT DISTINCT ON (idbucket) t3.bucket AS idbucket, t3.activitycount
> AS activitycount, t3.windowstart AS starttime, t3.windowend AS endtime
> FROM (...) t3
>
> In Postgresql, what this does is to return the FIRST entire row
> matching each distinct idbucket result. If Derby had a "FIRST()"
> aggregate function, it would be the equivalent of:
>
> SELECT t3.bucket AS idbucket, FIRST(t3.activitycount) AS
> activitycount, FIRST(t3.windowstart) AS starttime, FIRST(t3.windowend)
> AS endtime FROM (...) t3 GROUP BY t3.bucket
>
> Unfortunately, Derby has no such aggregate function. Furthermore, it
> would not be ideal if I were to do the work myself in ACF, because
> this is a resultset that needs to be paged through with offset and
> length, for presentation to the user and sorting, so it gets wrapped
> in another SELECT ... FROM (...) ORDER BY ... OFFSET ... LIMIT ...
> that does that part.
>
> Does anyone have any ideas and/or Derby contacts? I'd really like the
> quick-start example to have a functional set of reports.
>
> Karl
>
