2

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?

Anton Kumpan
  • 316
  • 2
  • 9
euraad
  • 2,467
  • 5
  • 30
  • 51

2 Answers2

2

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

Anton Kumpan
  • 316
  • 2
  • 9
  • Can I use it like this `SELECT * FROM data WHERE job_name = :jobName AND (select ROWNUM as rn, t.* from Table t) WHERE MOD(rn, 3) = 0 ORDER BY date_time ASC LIMIT :selectedLimit OFFSET :selectedOffset` – euraad Jan 10 '21 at 00:16
  • It must be an easier way to do this? – euraad Jan 10 '21 at 00:17
  • Something like this should work ``SELECT t.* FROM (select ROWNUM as rn, t.* from Table t) WHERE job_name = :jobName AND MOD(rn, 3) = 0 ORDER BY date_time ASC LIMIT :selectedLimit OFFSET :selectedOffset`` – Anton Kumpan Jan 10 '21 at 00:41
  • By the way. It's MySQL I'm using. – euraad Jan 10 '21 at 00:54
  • Found something now. This can print out row numbers only `SELECT * FROM (SELECT @row := @row + 1 AS rownum FROM (SELECT @row := 0) r, data) RANKED WHERE rownum % 2 = 1;` – euraad Jan 10 '21 at 01:14
1

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.

Dmitrii B
  • 2,672
  • 3
  • 5
  • 14