Is it possible to select all columns in SQL but only selecting for every third row?

user_1008405 0
Viewed 124

I want to select all columns, but each row is going to be selected for every X row.

For example, if I want to select all rows write like this:

@Transactional
@Query(value = "SELECT * FROM data WHERE job_name = :jobName ORDER BY date_time ASC LIMIT :selectedLimit OFFSET :selectedOffset" , nativeQuery = true)
List<Data> findByJobNameOrderByDateTimeAscLimit(@Param("jobName") String jobName, @Param("selectedOffset") long selectedOffset, @Param("selectedLimit") long selectedLimit);

But I want to extand this with something "For every X row" or something like that. Is that possible?

spring-data-jpa spring-boot jpa sql java
2 Answers

Approach with java code filter will select all data and filter it, which is not very efficient.

If you want to achieve that using SQL - query will depend on dialect you are using.

For example in Oracle - ROWNUM can be used, e.g.:

select *
  from (select ROWNUM as rn, t.*
        from Table t)
WHERE MOD(rn, 3) = 0

In code above - "3" is "X" from your question, meaning that "every 3rd row will be selected"

If you do not need "RN" in your results - use Select t.* in upper level query.

If your DB dialect does not support ROWNUM - you can add it yourself with wrapper query, following question should give you an idea how to do it: When i am using it with db2 for pagination, my next page is giving error

user_95838200

You can add in your invoking code this stream for example:

    int count[] = new int[1];
    List<Data> collect = dataList.stream()
            .peek(s -> count[0]++)
            .filter(s -> count[0] % 3 != 0)
            .collect(Collectors.toList());

Where'3' it's your "selectedLimit"

Also, you can create a common utils method with this code and wrapped your findByJobNameOrderByDateTimeAscLimit in your services and getting and filtering List<Data> with help code from example.

user_149270010
Related