Spring Data JPA: How to use DISTINCT with ORDER BY?

Question

I am running into ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Here are my Models & Query:

//Models: 

@Entity
@Table(name = "Book")
@Data
public class Book {
    @Id
    private String id;
    @OneToMany
    private List<Category> category;
}

@Entity
@Table(name="Category")
@Data
public class Category {
    @Id
    private int id;    
    private String category;
}

// 

@Repository
public interface BookDao extends JpaRepository<Book, Integer> {
    
    @Query("SELECT DISTINCT b FROM Book b join b.category c order by c.id DESC")
    Page<Book> getByBookIdDESC(Pageable pageable);




}

Things I've Tried: Providing b.category in the select.

    @Query("SELECT DISTINCT b, c.id FROM Book b join b.category c order by c.id DESC")
    Page<Book> getByBookIdDESC(Pageable pageable);

However, this is actually still providing duplicate results.

score 0 · Answer 1 · answered Jun 20 '22 at 02:49

0

Book has one-to-many relation with category and it is possible that a book belong to multiple categories so it is expected having duplicate books in resultset.

answered Jun 20 '22 at 02:49

Eskandar Abedini

2,090
2
13

hence why I'm attempting to use distinct..? – Sam KC Jun 20 '22 at 03:22
In mentioned case c.id is distinct but books in same category are duplicate, this is natural and distinct acted correctly on whole data including c.id and book data, Test the uniqueness by removing c.id from SELECT clause. – Eskandar Abedini Jun 20 '22 at 13:19
as you can see in my post, I've posted the code without the c.id in the SELECT clause. Thats how I originally did it and it returns: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list – Sam KC Jun 20 '22 at 17:20
You should remove ORDER BY Clause too. you are looking for distinct books or distinct categories and books in these distinct categories! in first case you should not use category entity in SELECT and ORDER BY clause in second case you can use category in both SELECT and ORDER BY clauses but you should expect repetitive books that belong to same category. – Eskandar Abedini Jun 20 '22 at 17:28

score 0 · Answer 2 · answered Jun 20 '22 at 02:55

0

Can you try with union ?

select distinct country
from (
    select country, theOrderColumn from table1
    union all
    select country, theOrderColumn from table2
) a 
order by theOrderColumn

answered Jun 20 '22 at 02:55

Adya

1,084
9
17

Spring Data JPA: How to use DISTINCT with ORDER BY?

2 Answers2