How to select from a table using HQL that are unique by one parameter?

Question

I need to select all unique rows from Feed table by reference_id (ref_id)

I wrote a request, but the request does not return unique values, but gives all the rows include duplicate rows with the same ref_id. So select doesn't work in right way

What is expected:

@Query("SELECT o FROM FeedEntity o WHERE o.referenceId IN (SELECT DISTINCT ol.referenceId FROM FeedEntity ol) AND o.id < :lastId")
List<FeedEntity> getByIdBefore(@Param("lastId") Long lastId, Pageable pageable);

This is the class that mapped for request:

public class FeedEntity {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  @OneToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "user_id")
  private UserEntity user;

  @Enumerated
  @Column(name = "type")
  private FeedType type;

  @Column(name = "ref_id")
  private Long referenceId;

  @Column(name = "create_time")
  private Timestamp createTime;

  public FeedEntity(UserEntity user, FeedType type, Long referenceId) {
     this.user = user;
     this.type = type;
     this.referenceId = referenceId;
     this.createTime = DateTimeUtil.getCurrentTimestamp();
  }
}

Native query is working

SELECT * FROM feed
WHERE id IN (
   SELECT DISTINCT MIN(Id)
   FROM feed fe
   GROUP BY fe.ref_id
)

What exactly `Feed` row from all with the same `ref_id` do you want to have in the result? — SternK, Sep 10 '20 at 14:00

score 0 · Answer 1 · answered Sep 10 '20 at 15:20

I hope you do realise you posted both the question and the answer This

SELECT o FROM FeedEntity o 
WHERE o.referenceId IN 
  (SELECT DISTINCT ol.referenceId 
   FROM FeedEntity ol) 
AND o.id < :lastId

is not the same as

SELECT * FROM feed
WHERE id IN (
   SELECT DISTINCT MIN(Id)
   FROM feed fe
   GROUP BY fe.ref_id
)

Why would you expect the same results.

You already have the query, just use it. The only difference I suggest based on the screenshot you provided is to swap out MIN for MAX as you wanted id 654 and 655 in your result while the MIN version would return 622 and 655.

@Query("SELECT o FROM FeedEntity o WHERE o.id IN (SELECT MAX(ol.id) FROM FeedEntity ol GROUP BY ReferenceId) AND o.id < :lastId")
List<FeedEntity> getByIdBefore(@Param("lastId") Long lastId, Pageable pageable);

How to select from a table using HQL that are unique by one parameter?

1 Answers1