DISTINCT ON

[Thorsten Schöning]

Hi there,

is there any way to create a "SELECT DISTINCT ON (a) a, b FROM (...)
ORDER BY (a)" from PostgreSQL? I recognized "selectDistinct", but
don't want all fields to be distinct, only one. Or does
"selectDistinct(...).select(...)" do the job? Didn't seem to work, but
I maybe have some unrelated problems elsewhere.

So is "DISTINCT ON" possible at all using jOOQ or not and if yes, how?

Postgres is the only RDBMS I need to support currently, so it's
perfectly fine if it only works with that. The only thing coming into
my mind would be to create the statement using jOOQ, afterwards use
toString to get a plain SQL string, insert "DISTINCT ON" where needed
and use execute(String) afterwards. But I would prefer native jOOQ
methods of course, especially because it's only such small difference
I need over the current "selectDistinct"...

Thanks for your help!

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail:Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Lukas Eder]

Hello Thorsten,

This SQL language extension is currently not supported, but it's been on the roadmap for a while:

https://github.com/jOOQ/jOOQ/issues/1410

I guess we could increase its priority, as it shouldn't be too hard to implement.

As a workaround, for the time being, you can use one of these hacks to embed this SQL snippet into your query:

Field<?> distinctOnA = DSL.field("distinct on({0}) {0}", TABLE.A.getDataType(), TABLE.A);

DSL.using(configuration)

   .select(distinctOnA, TABLE.B)

   .from(TABLE)

   .orderBy(TABLE.A)

Or:

Field<?> onA = DSL.field("on({0}) {0}", TABLE.A.getDataType(), TABLE.A);

DSL.using(configuration)

   .selectDistinct(onA, TABLE.B)

   .from(TABLE)

   .orderBy(TABLE.A)

That workaround pretends that the DISTINCT ON clause (example 1) or the ON clause (example 2) is part of the A column. Another workaround would be to use jOOQ's support for Oracle-style hints:

DSL.using(configuration)

   .select(TABLE.A, TABLE.B)

   .hint("distinct on(a)")

   .from(TABLE)

   .orderBy(TABLE.A)

Oracle-style hints are placed right after SELECT keyword (although before the DISTINCT keyword). Of course, you could also always resort to a more standard way of resolving this using GROUP BY:

DSL.using(configuration)

   .select(TABLE.A, max(TABLE.B))

   .from(TABLE)
   .groupBy(TABLE.A)

   .orderBy(TABLE.A)

Hope this helps,

Lukas

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

In the mean time, this has been implemented for jOOQ 3.5.0, available from GitHub. The API will look like this:

	public void testPostgresDistinctOnTest() {
	Result<Record2<Integer, Integer>> r1 =
	create().selectDistinct(T_BOOK.ID, T_BOOK.AUTHOR_ID)
	.on(T_BOOK.AUTHOR_ID)
	.from(T_BOOK)
	.orderBy(T_BOOK.AUTHOR_ID)
	.fetch();
	Result<Record2<Integer, Integer>> r2 =
	create().select(T_BOOK.ID, T_BOOK.AUTHOR_ID)
	.distinctOn(T_BOOK.AUTHOR_ID)
	.from(T_BOOK)
	.orderBy(T_BOOK.AUTHOR_ID)
	.fetch();
	assertEquals(r1, r2);
	assertEquals(asList(1, 3), r1.getValues(T_BOOK.ID));
	assertEquals(asList(1, 2), r1.getValues(T_BOOK.AUTHOR_ID));
	}

There are two alternative syntaxes, allowing to place the DISTINCT keyword before the select list or before the ON keyword. While it would have been possible to add a dedicated selectDistinctOn() method, that would have had one of these drawbacks:

- Loss of Record typesafety when chaining selectDistinctOn(...).select(...)

- API explosion with many new methods

I guess that the use-case is rather rare, so the slight change of syntax is acceptable.

Best Regards,

Lukas

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Montag, 18. August 2014 um 10:17 schrieben Sie:

> In the mean time, this has been implemented for jOOQ 3.5.0,
> available from GitHub.

Hello Lukas,

great news, thanks for the quick implementation.

> I guess that the use-case is rather rare, so the slight change of syntax is acceptable.

Your solution sounds absolutely good to me.