i am having bibles table where user read 3 bible chapters every day. My query works fine as from this answer . i have two columns in table id, chapter_name i.e below :-
id chapter_name 365 chapter_1 366 chapter_2 367 chapter_3 368 chapter_4 369 chapter_5 370 chapter_6 ... .... 1456 chater_1092
and after that i used below query
select bibles.portion_name,bibles.id, '2020-02-23' + interval (ceiling(dense_rank() over (order by id) / 3) - 1) day as read_date from bibles where id >= 365;
Its giving me blow output :-
id chapter_name read_date 365 chapter_1 2020-02-23 366 chapter_2 2020-02-23 367 chapter_3 2020-02-23 368 chapter_4 2020-02-24 369 chapter_5 2020-02-24 370 chapter_6 2020-02-24 ... .... 1454 chater_1090 2021-02-20 1455 chater_1091 2021-02-20 1456 chater_1092 2021-02-20
what i want when user requests for date 24 feb 2020 it must come 3 records. currenly its coming all records with actual read date. My expected output is like below:
id chapter_name read_date 368 chapter_4 2020-02-24 369 chapter_5 2020-02-24 370 chapter_6 2020-02-24
I have tried below query but its giving me error:-
select bibles.portion_name,bibles.id, '2020-02-23' + interval (ceiling(dense_rank() over (order by id) / 3) - 1) day as read_date from bibles where id >= 365 Having read_date ='2020-02-24';
ERROR IS
MySQL said: Documentation #4015 - Window function is allowed only in SELECT list and ORDER BY clause
Can anyone help me to resolve this issue?
Advertisement
Answer
You can use sub-query as below:
select * from (select bibles.portion_name,bibles.id, '2020-02-23' + interval (ceiling(dense_rank() over (order by id) / 3) - 1) day as read_date from bibles where id >= 365) as sub_tab where read_date ='2020-02-24';