what is the reason by rownum not performing on '=' & '>' operator.

Question

what is the reason by rownum not performing on '=' & '>' operator.

tusharchpatil

SSC Veteran

Points: 208
More actions
January 18, 2016 at 1:15 pm

#100600

Hi All,
If i used select * from emp where rownum < 5;
I am getting the correct output of table ie. 4 rows.
but it is not working on rownum > 5; and rownum = 5;
It gives the table structure only.
I am really intersted to know this reason why it is not work on = AND > operator?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

tusharchpatil (1/18/2016)
Hi All,
If i used select * from emp where rownum < 5;
I am getting the correct output of table ie. 4 rows.
but it is not working on rownum > 5; and rownum = 5;
It gives the table structure only.
I am really intersted to know this reason why it is not work on = AND > operator?

Your question is not very clear. What do you mean you get table structure only? And the proper syntax would not include a semicolon after the first condition. It could also be simplified to rownum >= 5.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

tusharchpatil (1/18/2016)
Hi All,
If i used select * from emp where rownum < 5;
I am getting the correct output of table ie. 4 rows.
but it is not working on rownum > 5; and rownum = 5;
It gives the table structure only.
I am really intersted to know this reason why it is not work on = AND > operator?

If you have tried

WHERE rownum > 5 and rownum = 5

You would never get any results, because rownum cannot simultaneously satisfy both conditions.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lowell SSC Guru Points: 323504 More actions · Answer 3

rownum would have to be a real column name, right?

if it is an alias for the row_number() functions's results, no results would occur if your table only had 4 rows in it.

SELECT * FROM (

SELECT row_number() over(order by name) As rownum

FROM (SELECT top 4 * from sys.tables)

)MyAlias WHERe rownum >=5

if the actual number of rows doesn't explain it, can you give more details?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tusharchpatil SSC Veteran Points: 208 More actions · Answer 4

no i am not using simultaneouly its writing mistake

i wanted to know why rownum not allowed = and > operator to get proper result.?

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

tusharchpatil (1/18/2016)
no i am not using simultaneouly its writing mistake
i wanted to know why rownum not allowed = and > operator to get proper result.?

I cannot understand this.

Please post your actual query.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Lowell SSC Guru Points: 323504 More actions · Answer 6

tusharchpatil (1/18/2016)
no i am not using simultaneouly its writing mistake
i wanted to know why rownum not allowed = and > operator to get proper result.?

it is absolutely allowed, and works just fine.

your specific implementation, however, is suspect; it could be due to the actual data queried, data types, or a number of other more obscure explanations.

without you providing the exact query you are using, we have to chalk it up to your inexperience, or bad data types, or some other end user inconsistency.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 7

i think this is an Oracle question ffor the psuedo column rownum that is materialized in that RDBMS:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns009.htm

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:
SELECT * FROM employees
WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

so that behaviour is by design.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

OP, do you understand the answer to your question?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions