Q: Spring Boot - Jpa Distinct and Pageable I have a Spring Boot v 2.1.8.RELEASE project that uses kotlin.
We use a postgres database and I have the following JPA query
    @Query(value = " SELECT distinct grievance " +
               " FROM Grievance grievance" +
               " WHERE (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId) " +
               " OR (grievance.status=:status AND grievance.auditProgramPortal=:auditProgramPortal)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.publicSubmission=:publicSubmission)")
fun findAnalystBodyGrievances(
        auditProgramPortal: Boolean,
        organizationId: Long,
        status: GrievanceStatus,
        publicSubmission: Boolean,
        pageable: Pageable): Page<Grievance>

When I run this as is - it works as fine. When I sort on the industry field (see object below) - I get the following error
org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Snippets from the relevant objects are as follows
Grievance
@Entity
@Audited
@Table(name = "grievance")
class Grievance {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null

    @Column(name = "title", nullable = false)
    var title: String? = null

    @Column(name = "description", columnDefinition = "text")
    var description: String? = null

    @Column(name = "public_description", columnDefinition = "text")
    var publicDescription: String? = null

    @Column(name = "related_to_identified_risk")
    var relatedToIdentifiedRisk: Boolean? = null


    @OneToOne
    @JoinColumn(name = "industry_id")
    var industry: Industry? = null
  }

Industry
@Entity
@Table(name = "industry")
class Industry {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    var id: Long? = null

    @Column(name = "name", nullable = false, unique = true)
    var name: String? = null

    @Column(name = "color", nullable = false, unique = false)
    var color: String? = null
}

My question is - what is the easiest way to fix the query so that a Page is still returned
If I update my code to use the following Query - A generic object is returned
@Query(value = " SELECT distinct grievance, grievance.industry " +
               " FROM Grievance grievance" +
               " WHERE (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId) " +
               " OR (grievance.status=:status AND grievance.auditProgramPortal=:auditProgramPortal)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId)" +
               " OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.publicSubmission=:publicSubmission)")

Any help on this is much appreciated

A: try below
@Query(value =" Select grievance from (SELECT distinct grievance, grievance.industry " +
" FROM Grievance grievance" +
" WHERE (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId) " +
" OR (grievance.status=:status AND grievance.auditProgramPortal=:auditProgramPortal)" +
" OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.owner.id=:organizationId)" +
" OR (grievance.auditProgramPortal=:auditProgramPortal AND grievance.publicSubmission=:publicSubmission))")

