Skip to content
Advertisement

fetch records based on mysql alias using having clause

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';
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement