1

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?

Improve this question
4
  • How much data do you have? If it's not a ton, I would query the DB for all the data. I would then use code to find the highest power and the highest high. I am not sure how pagination works with Spring, so I am not going to comment on that. Commented Dec 5, 2023 at 1:52
  • The data might be hunderds, like there is no delete operation on the data, but there is date expiration, so data will be exclude by using the date < now(). Commented Dec 5, 2023 at 2:08
  • Sounds like a small memory amount. It should be doable. Commented Dec 5, 2023 at 2:10
  • I think no. The problem is 1. the API might have big number of hit, 2. the logic of pagination containing page_number and page_size and sorting query. I will update the question why is it not doable by using Java to loop and do the logic Commented Dec 5, 2023 at 2:27

1 Answer 1

Reset to default
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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

can I ask is there any analysis on performance for large data like hundreds rows or thousands row
You will have to run the query to see how long it takes. I am not sure.
cool, this answer what I wanted, and works with the Java JPA native query

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.