CODEWITHSUNDEEP
sqlsql-server
user3112991
user3112991

Reputation: 31

ROW_NUMBER and multiple JOIN

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?

Upvotes: 1

Views: 1619

Answers (4)

anbisme
anbisme

Reputation: 163

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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;

Upvotes: 1

Karl Kieninger
Karl Kieninger

Reputation: 9139

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

Upvotes: 1

Anup Agrawal
Anup Agrawal

Reputation: 6669

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

Upvotes: 1

Related Questions