SQL ROW_NUMBER with INNER JOIN

Question

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

This looks a little homework-y - what have you *tried*? Did it result in an error? "Wrong" results? You're *apparently* already aware that `ROW_NUMBER` is the correct function to perform paging... — Damien_The_Unbeliever, Mar 17 '12 at 17:57
@Damien_The_Unbeliever he has provided the query. Sorry but just want to be reasonable. It is a valid question. — TheTechGuy, Sep 27 '12 at 12:41

score 11 · Answer 1 · answered Mar 17 '12 at 17:58

11

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

answered Mar 17 '12 at 17:58

Martin Smith

438,706
87
741
845

Actually you could use * : [[ SELECT ROW_NUMBER() OVER (ORDER BY ...) AS [RN], * FROM ...]] – Learner Apr 15 '13 at 07:37
@Cristi - The reason they can't use `*` is because the column name `starRating` appears in both tables and column names in CTEs must be unique. – Martin Smith Apr 15 '13 at 08:14
oops, did not pay attention to that, sorry! – Learner Apr 15 '13 at 19:03

score 1 · Answer 2 · answered Mar 17 '12 at 18:06

1

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;

answered Mar 17 '12 at 18:06

Chetter Hummin

6,687
8
32
44

This one brings back a result. Thanks! I have a question... Can I execute this from ASP? I've tried adding it to my page code with a standard "oDatabase.SQL = "WITH t AS (SELECT..." but its not returning any rows. Am I missing something? Apologies if this is a stupid question – Jason Eyebe Mar 17 '12 at 18:24
@JasonEyebe Sorry, but I'm not familiar with ASP at all. – Chetter Hummin Mar 17 '12 at 18:25

SQL ROW_NUMBER with INNER JOIN

2 Answers2