"FIRST based on which sort?"? First based on the existing sort, which is crucial, because the sort is by bucket ASC, activitycount DESC. I'm looking for the row with the highest activitycount, per bucket.
The other thing is that we cannot afford to use the same "table" twice, as it is actually an extremely expensive query in its own right, with multiple joins, select distinct's, etc. under the covers. I'd be happy to post it but it may shock you. ;-) Karl On Sun, Sep 19, 2010 at 11:32 AM, Alexey Serba <ase...@gmail.com> wrote: >> 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 >> >
