CODEWITHSUNDEEP
sqlsql-serverrow-number
Jason Eyebe
Jason Eyebe

Reputation: 161

SQL ROW_NUMBER with INNER JOIN

I need to use ROW_NUMBER() in the following Query to return rows 5 to 10 of the result. Can anyone please show me what I need to do? I've been trying to no avail. If anyone can help I'd really appreciate it.

SELECT * 
FROM   villa_data 
       INNER JOIN villa_prices 
         ON villa_prices.starRating = villa_data.starRating 
WHERE  villa_data.capacity >= 3 
       AND villa_data.bedrooms >= 1 
       AND villa_prices.period = 'lowSeason' 
ORDER  BY villa_prices.price, 
          villa_data.bedrooms, 
          villa_data.capacity

Upvotes: 5

Views: 26338

Answers (2)

Chetter Hummin
Chetter Hummin

Reputation: 6817

You can use a with clause. Please try the following

WITH t AS
(
SELECT villa_data.starRating, 
   villa_data.capacity,
   villa_data.bedrooms,
   villa_prices.period,
   villa_prices.price,
   ROW_NUMBER() OVER (ORDER BY villa_prices.price, 
      villa_data.bedrooms, 
      villa_data.capacity ) AS 'RowNumber'
FROM   villa_data 
   INNER JOIN villa_prices
     ON villa_prices.starRating = villa_data.starRating 
WHERE  villa_data.capacity >= 3 
   AND villa_data.bedrooms >= 1 
   AND villa_prices.period = 'lowSeason' 
)
SELECT * 
FROM t 
WHERE RowNumber BETWEEN 5 AND 10;

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453327

You need to stick it in a table expression to filter on ROW_NUMBER. You won't be able to use * as it will complain about the column name starRating appearing more than once so will need to list out the required columns explicitly. This is better practice anyway.

WITH CTE AS
(
SELECT /*TODO: List column names*/
       ROW_NUMBER() 
          OVER (ORDER BY villa_prices.price, 
                         villa_data.bedrooms, 
                         villa_data.capacity) AS RN
FROM   villa_data 
       INNER JOIN villa_prices 
         ON villa_prices.starRating = villa_data.starRating 
WHERE  villa_data.capacity >= 3 
       AND villa_data.bedrooms >= 1 
       AND villa_prices.period = 'lowSeason' 

)
SELECT /*TODO: List column names*/
FROM CTE
WHERE RN BETWEEN 5 AND 10
ORDER BY RN

Upvotes: 11

Related Questions