Using row_number on attribute to only return 5 rows

[webdeveloper123]

Hi guys,

I am trying to limit an attribute in an sql select statement to only return 5 rows. I've been researching it and apparently I have to use row_number. I am trying to limit description as this has the facilities for the hotel room. There could be anywhere bewteen 6 and 14 , but I only want to show a maximum of 5. I have got this far:

  SELECT *
FROM 
    (SELECT description,
         facility_id,
         row_number()
        OVER (order by facility_id) AS row_num
    FROM facility) f
WHERE  row_num <= 5;

It seems to be giving me the correct results. But now I have to merge it into this:

SELECT rs.description
     , rs.sleeps
     , rs.image
     , rs.price
     , GROUP_CONCAT(DISTINCT f.description SEPARATOR ' • ') AS rmfac
     , rt.description as rmtype
     , r.room_id
     , r.room_number
FROM room as r 
     JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id
     JOIN roomsize as rs ON r.size_id = rs.size_id
     JOIN room_facility as rf ON rf.size_id = rs.size_id
     JOIN facility as f ON rf.facility_id = f.facility_id
     LEFT JOIN room_booking as rb ON r.room_id = rb.room_id
                                  AND rb.departureDate > :arrival
                                  AND rb.arrivalDate <= :departure
WHERE rb.room_id IS NULL 
GROUP BY room_id;

I have tried several ways including this:

SELECT rs.description
     , rs.sleeps
     , rs.image
     , rs.price
     , GROUP_CONCAT(DISTINCT f.description SEPARATOR ' • ') AS rmfac
     , row_number()
        OVER (order by f.facility_id) AS row_num
     , rt.description as rmtype
     , r.room_id
     , r.room_number
FROM room as r 
     JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id
     JOIN roomsize as rs ON r.size_id = rs.size_id
     JOIN room_facility as rf ON rf.size_id = rs.size_id
     JOIN facility as f ON rf.facility_id = f.facility_id
     LEFT JOIN room_booking as rb ON r.room_id = rb.room_id
                                  AND rb.departureDate > '2024-01-01'
                                  AND rb.arrivalDate <= '2024-01-05' 
WHERE rb.room_id IS NULL 
GROUP BY room_id;
WHERE  row_num <= 5;

But I get the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE  row_num <= 5' at line 1

At first I thought it was because I had 2 x WHERE clauses, so I just joined them together using AND I still get an error.

Is ORDER BY and GROUP BY clashing?

Can someone help please?

Thanks

[webdeveloper123]

Sorry I noticed an error in the above code which doesn't run. I have an extra ; at the end of GROUP BY then have another ; after WHERE row_num <=5. Got rid of it but still the same error

[requinix]

WHERE rb.room_id IS NULL 
GROUP BY room_id
WHERE  row_num <= 5;

If I point out those three lines on their own, can you see the problem?

[webdeveloper123]

No, sorry I can't. Is it because there are 2 x WHERE clauses? Or is it in the wrong order? It's something blatantly obvious isn't it? 

[Barand]

2 minutes ago, webdeveloper123 said:

Is it because there are 2 x WHERE clauses? Or is it in the wrong order?

Both

[webdeveloper123]

9 minutes ago, Barand said:

Both

Would it be correct in this situation to Join the WHERE clauses using AND?

[Barand]

Yes. A single WHERE preceding the GROUP BY

[webdeveloper123]

I got to this:

WHERE  row_num <= 5 AND  rb.room_id IS NULL
GROUP BY room_id;

But now I get another error:

#1054 - Unknown column 'row_num' in 'where clause'

Is my row_number code even correct?

[Barand]

You cannot use a column alias in a WHERE clause, you need the whole expression

[webdeveloper123]

I don't get it. I have to put the whole row_number code in the WHERE clause?

[webdeveloper123]

Actually, did you mean swap out row_num for f.description?

[webdeveloper123]

I did swap them out, I am getting search results but some of them it limits to 5 facilities shown, some of them it doesn't. 

[Barand]

1 hour ago, webdeveloper123 said:

I don't get it. I have to put the whole row_number code in the WHERE clause?

Yes. Aliases don't exist at the time the query uses the WHERE clause - they are created on output.

So...

WHERE rb.room_id IS NULL 
      AND row_number() OVER (order by f.facility_id) <= 5

 

[webdeveloper123]

Hey Barand,

Thanks for the code but sorry I'm getting a bit confused. I'm using:

SELECT rs.description
     , rs.sleeps
     , rs.image
     , rs.price
     , GROUP_CONCAT(DISTINCT f.description SEPARATOR ' • ') AS rmfac
     , rt.description as rmtype
     , r.room_id
     , r.room_number
FROM room as r 
     JOIN roomtype as rt ON  r.roomtype_id = rt.roomtype_id
     JOIN roomsize as rs ON r.size_id = rs.size_id
     JOIN room_facility as rf ON rf.size_id = rs.size_id
     JOIN facility as f ON rf.facility_id = f.facility_id
     LEFT JOIN room_booking as rb ON r.room_id = rb.room_id
                                  AND rb.departureDate > :arrival
                                  AND rb.arrivalDate <= :departure



WHERE rb.room_id IS NULL 
      AND row_number() OVER (order by f.facility_id) <= 5
GROUP BY room_id;

I tried it several ways, different order of code but keep getting the same error:

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 4015 Window function is allowed only in 
SELECT list and ORDER BY clause in /var/www/vhosts/:86 Stack trace: #0 
/var/www/vhosts/(86): PDO->prepare() #1 {main} thrown in /var/www/vhosts/ on line 86

Thanks

[Barand]

Try getting your 5 facilities for each room in a table subquery. Then join to that subquery on room_id.

[webdeveloper123]

I don't get it. I thought that 

42 minutes ago, Barand said:

So...

WHERE rb.room_id IS NULL 
      AND row_number() OVER (order by f.facility_id) <= 5

Was the last little bit of code? 

[Barand]

It would be if it allowed window functions in WHERE clauses - but it doesn't, so some rewriting needed..

[webdeveloper123]

So would I have to base it on something like this:

  SELECT *
FROM 
    (SELECT description,
         facility_id,
         row_number()
        OVER (order by facility_id) AS row_num
    FROM facility) f
WHERE  row_num <= 5;

From the top of the thread?

[webdeveloper123]

46 minutes ago, Barand said:

Try getting your 5 facilities for each room in a table subquery. Then join to that subquery on room_id.

I don't even know how to do that, this is going to take me forever

[Barand]

Another way to it is to forget about row_number() OVER etc and, instead, use the simpler

SELECT ...
     , substring_index(GROUP_CONCAT(f.description separator ' • '), ' • ', 5) as rmfac

to get the whole list of facilies then select the first 5

[webdeveloper123]

Thanks for the code Barand, I'll give it a try

[webdeveloper123]

I can't believe it! It does it! 😄

Thanks Barand!