ROW_NUMBER and multiple JOIN

Question

When I test this request:

SELECT 
    *, ROW_NUMBER() OVER (ORDER BY test1) AS lineNumb 
FROM 
    (SELECT DISTINCT 
        tab1.test1, tab2.test2
     FROM TB_tab1 tab1
     JOIN TB_tab2 tab2 ON tab2.test3 = tab1.test3 
     JOIN TB_tab3 tab3 ON tab3.test4 = tab1.test4 
     WHERE tab3.test5 != 'test') AS sub
WHERE lineNumb BETWEEN 1 AND 5

I get:

Error: column name invalid : 'lineNumb'. SQLState: S0001 ErrorCode: 207

Why and how can I correct my request?

This [previous question](http://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause) should help you rewrite the query. — user2989408, Apr 21 '14 at 16:38

score 1 · Answer 1 · answered Apr 21 '14 at 16:38

SELECT *
FROM 
(
SELECT *, ROW_NUMBER() OVER (ORDER BY test1) AS lineNumb FROM (
SELECT DISTINCT tab1.test1, tab2.test2
FROM TB_tab1 tab1
JOIN TB_tab2 tab2 ON tab2.test3 = tab1.test3 
JOIN TB_tab3 tab3 ON tab3.test4 = tab1.test4 WHERE tab3.test5 !='test') as sub
) as sub2 
WHERE lineNumb BETWEEN 1 AND 5

score 1 · Accepted Answer · answered Apr 21 '14 at 16:38

Why:

You cannot access the aliased lineNumb column in the where clause of your select--it isn't defined in the context.

How to correct:

Use what you have as a subquery or CTE and select from that using your where.

SELECT * 
  FROM (<you existing query without the where>)
 WHERE lineNumb <= 5

score 1 · Answer 3 · answered Apr 21 '14 at 16:42

You cannot use a column alias in a where at the same level. You can do what you want with analytic functions. You don't even need the distinct:

SELECT *
FROM (SELECT tab1.test1, tab2.test2,
             ROW_NUMBER() OVER (PARTITION BY tab1.test1, tab2.test2 ORDER BY test1) as seqnum,
             DENSE_RANK() OVER (ORDER BY test1) as lineNumb
      FROM TB_tab1 tab1
      JOIN TB_tab2 tab2 ON tab2.test3 = tab1.test3 
      JOIN TB_tab3 tab3 ON tab3.test4 = tab1.test4
      WHERE tab3.test5 <> 'test'
     ) sub
WHERE lineNumb BETWEEN 1 AND 5 AND seqnum = 1;

score 1 · Answer 4 · edited May 23 '17 at 11:49

1

Check out the order of execution in a SELECT statement in this question:

What's the execute order of the different parts of a SQL select statement?

The SELECT clause isn't read until late in the execution of the statement. So, you can't reference the alias until you get to the ORDER BY clause.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 21 '14 at 16:53

anbisme

163
2
11

ROW_NUMBER and multiple JOIN

4 Answers4