row_number() is not working properly

_{Tuesday, March 22, 2011 3:26 PM}

I tried with following sql command in mssql server 2005(japanese version)

create table mytab ( Place VARCHAR(20))
INSERT INTO mytab values ('First Record')
INSERT INTO mytab values ('Second Record')
INSERT INTO mytab values ('Third Record')
INSERT INTO mytab values ('Fourth Record')
INSERT INTO mytab values ('Final Record')

I wrote the following commands after insert some datas:

select row_number() over (order by Place) as ROWNUM, Place from mytab

And I got following output:

ROWNUM PLACE
1 Final Record
2 First Record
3 Fourth Record
4 Second Record
5 Third Record

But When i tried the following querythen it is showing "The column name 'ROWNUM' is invalid. " type error.

select row_number() over (order by Place) as ROWNUM, Place from mytab where ROWNUM>1

Actually I didn't understand why this is happening? PLease give me the solutions. Already I waste lot of times for this.

Is there anyone who can give me the solution.

Thanks in advance

selocous

_{Tuesday, March 22, 2011 3:34 PM ✅Answered}

You can not use computed column in the WHERE condition, you can only use it in the ORDER BY. Please read answers on this quiz question to understand why.

In order to solve your problem you can use derived table approach, e.g.

select * from 
  (select ROW_NUMBER() OVER (ORDER BY Place) as RowNum, 
       Place
  FROM myTab) X 
WHERE RowNum > 1

For every expert, there is an equal and opposite expert. - Becker's Law

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Tuesday, March 22, 2011 3:35 PM}

The row_number function or any ranking function is applied to the set entering the SELECT clause, so you cannot reference them in previous logical phases.

FROM (JOIN / APPLY / PIVOT / UNPIVOT)
WHERE
GROUP BY
HAVING
SELECT - DISTINCT
SELECT - TOP
ORDER BY

Use a derived table or a CTE.

select c1, .., cn
from (select c1, ..., cn, row_number() over(order by place) as rownum from ...) as T
where rownum > 1;

My good friend Brad Shulz, just bloged about this in specific a few days ago.

Who Am I? Where Do I Fit In?

AMB

Some guidelines for posting questions...

_{Tuesday, March 22, 2011 4:10 PM}

YOu Need to understand the query processing sequence first.

In Query processing the Select phase is processed after the where clause and therefore, for your instance the where clause doesn't know what is rownum yet.

You can use a derived table or a common table expression first and it should work

_{Wednesday, March 23, 2011 2:06 AM}

Naomi,

Thanks for your help.

It works.

Thanks

selocous

_{Wednesday, March 23, 2011 3:04 AM}

You do not understand SQL. Rows are not records. Rows have no order in a table. A good SQL programmer would have written this code:

CREATE TABLE My_Table (place VARCHAR(20) NOT NULL PRIMARY KEY);

A table must have a key.

INSERT INTO My_Table
VALUES ('First Row'),
    ('Second Row'),
    ('Third Row'),
    ('Fourth Row'),
    ('Final Row');

>> I wrote the following commands [sic: statements] after insert some data. <<

Commands exist in procedural programming languages; SQL has statements because SQL is declarative.

SELECT ROW_NUMBER() OVER (ORDER BY place) AS place_text_seq, place
 FROM My_Table;

“place” is text, so ORDER BY place is a character sort. That is what you got

But When I tried the following query then it is showing "The column name 'place_text_sort' is invalid. " type error.

SELECT ROW_NUMBER() OVER (ORDER BY place) AS place_text_seq, place
  FROM My_Table
 WHERE place_text_sort > 1;

A function call cannot be used as a column name at this level of aggregation. The SELECT list is done last in a query. The computation has to get a name at a higher level. Try this:

SELECT X.place_text_sort, X.place
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY place), place
          FROM My_Table)
       AS X(place_text_sort, place);
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL