I get the point. Thanks for the detailed explanation! Am Sonntag, 17. Februar 2013 17:07:48 UTC+1 schrieb Lukas Eder: > > 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: > > ID TITLECOUNT(*)OVER() 2Animal Farm 3 3 O Alquimista 3 4Brida 3 > 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] <javascript:>> > >> 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] <javascript:>. >> 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.
