Example: -- get all authors' first and last names, and the number -- of books they've written in German, if they have written -- more than five books in German in the last three years -- (from 2011), and sort those authors by last names -- limiting results to the second and third row SELECT T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME, COUNT(*) FROM T_AUTHOR JOIN T_BOOK ON T_AUTHOR.ID = T_BOOK.AUTHOR_ID WHERE T_BOOK.LANGUAGE = 'DE' AND T_BOOK.PUBLISHED > '2008-01-01' GROUP BY T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME HAVING COUNT(*) > 5 ORDER BY T_AUTHOR.LAST_NAME ASC NULLS FIRST LIMIT 2 OFFSET 1 FOR UPDATE OF FIRST_NAME, LAST_NAME NO WAIT
create.select(TAuthor.FIRST_NAME, TAuthor.LAST_NAME, create.count()) .from(T_AUTHOR) .join(T_BOOK).on(TBook.AUTHOR_ID.equal(TAuthor.ID)) .where(TBook.LANGUAGE.equal("DE")) .and(TBook.PUBLISHED.greaterThan(parseDate('2008-01-01'))) .groupBy(TAuthor.FIRST_NAME, TAuthor.LAST_NAME) .having(create.count().greaterThan(5)) .orderBy(TAuthor.LAST_NAME.asc().nullsFirst()) .limit(2) .offset(1) .forUpdate() .of(TAuthor.FIRST_NAME, TAuthor.LAST_NAME) .noWait();
| |