January 18, 2016 at 1:15 pm
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?
January 18, 2016 at 1:17 pm
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/
January 18, 2016 at 1:20 pm
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.
January 18, 2016 at 1:20 pm
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
January 18, 2016 at 2:25 pm
no i am not using simultaneouly its writing mistake
i wanted to know why rownum not allowed = and > operator to get proper result.?
January 18, 2016 at 2:29 pm
tusharchpatil (1/18/2016)
no i am not using simultaneouly its writing mistakei 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.
January 18, 2016 at 2:34 pm
tusharchpatil (1/18/2016)
no i am not using simultaneouly its writing mistakei 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
January 18, 2016 at 2:43 pm
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
January 19, 2016 at 5:06 am
OP, do you understand the answer to your question?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply