Select Distinct

Definition

The selectDistinct methods allows you to add the SELECT DISTINCT clause to the query.

Available methods

1. selectDistinct(KColumnAllowedToSelect... kColumnsAllowedToSelect)

• kColumnsAllowedToSelect: are all the expresions that will be added to the SELECT DISTINCT clause.
  Among the possible values are: KTableColumn, KColumn, Values, KCondition, Columns with over, Columns with alias, KRaw, Case conditional expression.

2. selectDistinct(KQuery kQuery, String alias)

• kQuery: is a subquery which will be added to the SELECT DISTINCT list and treated as a column.
• alias: which is the alias of the previously mentioned column.

Method hierarchy

The selectDistinct method can be used right after the following methods or objects:

• KBuilder, with, withRecursive

and the subsequent methods that can be called are:

• select, from, where, groupBy, window, except, exceptAll, intersect, intersectAll, union, unionAll, orderBy, limit, offset, fetch, single, multiple

Example: KColumnAllowedToSelect...

Java code:

k
.selectDistinct(
    APP_USER.ID,
    concat(APP_USER.FIRST_NAME, val(" "), APP_USER.LAST_NAME).as("fullName"),
    coalesce(APP_USER.LAST_NAME, APP_USER.FIRST_NAME),
    val(7),
    APP_USER.FIRST_NAME.isNull(),
    raw("au.role_id"),
    caseConditional()
        .when(APP_USER.CREATED_AT.gt(LocalDateTime.now().minusDays(7))).then(APP_USER.EMAIL)
        .elseResult(val("No email available"))
        .end()
        .as("email"),
    rowNumber().over(wd().orderBy(APP_USER.ID)).as("order")
)
.from(APP_USER)
.multiple();

SQL generated:

SELECT DISTINCT
    au.id,
    CONCAT(au.first_name || ?1 || au.last_name) AS "fullName",
    COALESCE(au.last_name, au.first_name),
    ?2,
    au.first_name IS NULL,
    au.role_id,
    CASE WHEN au.created_at > ?3 THEN au.email ELSE ?4 END AS "email",
    ROW_NUMBER() OVER(ORDER BY au.id) AS "order"
FROM app_user au

Parameters:

• ?1: " "
• ?2: 7
• ?3: 2022-12-20T20:07:35.988714
• ?4: "No email available"

Example: kQuery, alias

Java code:

final KQuery subQuery =
    k
    .select(count())
    .from(APP_USER_SPECIALTY)
    .innerJoin(APP_USER_SPECIALTY.joinAppUser());

k
.selectDistinct(subQuery, "countSpecialties")
.select(APP_USER.ID)
.from(APP_USER)
.multiple();

SQL generated:

SELECT DISTINCT
    (
        SELECT COUNT(*)
        FROM app_user_specialty aus
        INNER JOIN app_user au ON (aus.app_user_id = au.id)
    ) AS countSpecialties,
    au.id
FROM app_user au

Parameters:

• None