Select specific number of sub items

I have this collection of data that I made a query on it

Hotel_Id  | User_Id | Comment       | Date
1           1         Hello world     2020-1-1
1           2         Hello world     2020-1-1
1           3         Hello world     2020-1-1
1           4         Hello world     2020-1-1
1           5         Hello world     2020-1-1
2           1         Hello world 2   2020-1-1
2           2         Hello world 2   2020-1-1
2           3         Hello world 2   2020-1-1
2           4         Hello world 2   2020-1-1
2           5         Hello world 2   2020-1-1

I want to make my query like this:

Hotel_Id  | User_Id | Comment       | Date
1           1         Hello world     2020-1-1
1           2         Hello world     2020-1-1
1           3         Hello world     2020-1-1
2           1         Hello world 2   2020-1-1
2           2         Hello world 2   2020-1-1
2           3         Hello world 2   2020-1-1

Query:

SELECT r.Hotel_Id, r.User_Id, r.Comment, r.Date
FROM rating r
JOIN hotel h
ON r.Hotel_Id = h.Id
JOIN brand b
ON h.Brand_Id = b.Id
WHERE b.Id = 1
ORDER BY r.Hotel_Id, r.User_Id

There are multiple brands and each brand has collection of hotels and each hotel must have only three comment not all comments that it has in db what is the type of condition that I should put it to get this data thank for your help

Answer

You can give a row number and then select the records having the row number upto 3.

Query

select `t`.`hotel_id`, `t`.`user_id`, `t`.`comment`, `t`.`date` from (
   select `hotel_id`, `user_id`, `comment`, `date`, ( 
       case `hotel_id` 
       when @curA
       then @curRow := @curRow + 1 
       else @curRow := 1 and @curA := `hotel_id` end
    ) as `rn`
    from `your_table_name`,
    (select @curRow := 0, @curA := '') as `r`
    order by `hotel_id`, `user_id`
) as `t`
where `t`.`rn` <= 3;

SQL Fiddle Here

Advertisement

Answer