CODEWITHSUNDEEP
sql-serverwhere-clausecommon-table-expressionrow-number
Francesco Mantovani
Francesco Mantovani

Reputation: 12207

SQL Server: ROW_NUMBER() in WHERE clause returns "Invalid column name"

Since a few days I'm working with this free and open source database that lists all IP addresses in the World.

My goal is to create a database that could list:

  1. All country_code in the world
  2. All city_name in the world taken once
  3. Latitude and longitude of each city
  4. A countdown order desc on the country_code

and I did it:

SELECT 
ROW_NUMBER() OVER (ORDER BY country_code desc,city_name desc) as countdown_order,
AVG(latitude) AS latitude, 
AVG(longitude) AS longitude, 
city_name, 
country_code
FROM ip2location_db11 
--where countdown_order < '100'
GROUP BY country_code, city_name
ORDER BY country_code, city_name

enter image description here

Problems arrives when I uncomment that where countdown_order < '100' The query returns me 

Msg 207, Level 16, State 1, Line 8 
Invalid column name 'countdown_order'.

enter image description here

Yes, I tried using CTE but it returns me more errors because of the ORDER BY.

I'm not sure what to try next.

Upvotes: 0

Views: 1424

Answers (2)

Kashyap MNVL
Kashyap MNVL

Reputation: 603

You could also do it using Common Table Expressions (CTEs). It looks something like this:

;WITH MAIN_CTE AS (
SELECT 
          ROW_NUMBER() OVER (ORDER BY country_code desc, 
                                      city_name desc) as countdown_order,
          AVG(latitude) AS latitude, 
          AVG(longitude) AS longitude, 
          city_name, 
          country_code   
    FROM  ip2location_db11 
    GROUP BY country_code, city_name
) 
SELECT * FROM MAIN_CTE 
WHERE countdown_order < 100
ORDER BY country_code, city_name

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

countdown_order is a column alias. You can't reference to the column alias at the same level.

However, you can do it at a higher level like a derived table or cte

SELECT *
FROM
(
    SELECT 
          ROW_NUMBER() OVER (ORDER BY country_code desc, 
                                      city_name desc) as countdown_order,
          AVG(latitude) AS latitude, 
          AVG(longitude) AS longitude, 
          city_name, 
          country_code   
    FROM  ip2location_db11 
    GROUP BY country_code, city_name
) as D
where countdown_order < 100   -- countdown_order is an integer, remove the single quote
ORDER BY country_code, city_name

Upvotes: 3

Related Questions