How to select all row and give boolean mark the row with max value of a column (with multiple marking)? (Java Native Query, Psql)

Question

Currently I am working on giving a label to my data. I will give the label to data that marked as highest on the resulting query. the problem is the label is not only for 1 case but multiple, and also I need pagination for the query using Pageable.

*Note: I need it by query because I think it's not feasible to do in Java because 1. the API might have big number of hit, 2. the logic of pagination containing page_number and page_size and sorting query.

---

Example of data

id	name	power	heigh
uuid1	ace	1000	170
uuid2	luffy	990	168
uuid3	zorro	980	167
uuid4	sanji	970	180

---

Mocked result that I wanted when I queried is like this

id	name	power	is_highest_power	heigh	is_highest_heigh
uuid1	ace	1000	true	170	false
uuid2	luffy	990	false	168	false
uuid3	zorro	980	false	167	false
uuid4	sanji	970	false	180	true

---

Currently, working on it with Postgresql db with java JPARepository interface. I need to build the native query with pagination in it and also a filter for the result.

@Query(
      nativeQuery = true,
      countQuery = "SELECT count(1) "
          + " FROM user u "
          + " WHERE (:startPower IS NULL OR u.power >= :startPower) AND "
          + " (:startHeigh IS NULL OR u.heigh >= :startHeigh)",
      value = "SELECT u.id, u.name, u.power, u.is_highest_power (??), u.heigh, "
          + " u.is_highest_heigh (??)"
          + " FROM user u "
          + " WHERE (:startPower IS NULL OR u.power >= :startPower) AND "
          + " (:startHeigh IS NULL OR u.heigh >= :startHeigh)"
  )
  Page<SearchUser> searchUser(
      Pageable pageable,
      BigDecimal startPower,
      BigDecimal startHeigh
  );


public interface SearchUser {

  @Value("#{target.id}")
  String getId();

  @Value("#{target.name}")
  String getName();

  @Value("#{target.power}")
  BigDecimal getPower();

  @Value("#{target.is_highest_power}")
  Boolean getIsHighestPower();

  @Value("#{target.heigh}")
  BigDecimal getHeigh();

  @Value("#{target.is_highest_heigh}")
  Boolean getIsHighestHeigh();

I have found how to query to get the highest power or heigh (as mentioned here) but can not found how to mark a row as the highest and query it as the result column too.

How to achieve this in nativeQuery string?

Answer 1

See if using CASE WHEN helps.

SELECT id, name, power, 
CASE WHEN (SELECT MAX(power) FROM user) = power THEN true ELSE false END AS is_highest_power,
heigh,
CASE WHEN (SELECT MAX(heigh) FROM user) = heigh THEN true ELSE false END AS is_highest_heigh
FROM user

Output