Check out this sample query (also on http://sqlfiddle.com/#!4/d41d8/7433)
WITH t(id, title) AS ( SELECT 1, '1984' FROM DUAL UNION ALL SELECT 2, 'Animal Farm' FROM DUAL UNION ALL SELECT 3, 'O Alquimista' FROM DUAL UNION ALL SELECT 4, 'Brida' FROM DUAL ) SELECT t.id, t.title, COUNT(*) OVER() FROM t WHERE t.title LIKE '%a%' It features parts of the jOOQ T_BOOK table. The actual query is to select ID and TITLE where TITLE contains at least one 'a' character. Instead of running this query twice in order to fetch the number of results that would be obtained by the [title like '%a%'] predicate, you can just add this window function to the regular projection. The number of results is thus added to every record: IDTITLECOUNT(*)OVER()2Animal Farm33O Alquimista34Brida3 jOOQ supports this window function as count().over(). As in the SQL standard, all jOOQ aggregate functions can be turned into window functions using the OVER() clause: http://www.jooq.org/javadoc/latest/org/jooq/AggregateFunction.html#over() Of course, this might not always be implemented in all SQL dialects. Among the databases supported by jOOQ, these databases support window functions: - CUBRID - DB2 - Postgres - Oracle - SQL Server - Sybase SQL Anywhere More information can be found here: http://www.jooq.org/doc/2.6/manual/sql-building/column-expressions/window-functions/ Cheers Lukas 2013/2/17 <[email protected]> > Thanks for the update! > > How would a query with the windowing function Count(*) Over() look like? > Does it somehow provide the total number of rows before you actually start > processing the records? > > thanks! > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
