rownum w/ NOT IN

Hi,

SQL> select fname, lname, rownum
2 from sample;

FNAME LNAME ROWNUM
---- ---- ----
John Smith 1
John Smith 2

I wish to delete one of the above rows. Could someone please tell me what is happening below

SQL> select *
2 from sample
3 where rownum IN
4 (select rownum
5 from sample);

FNAME LNAME
---- ----
John Smith
John Smith

SQL> select *
2 from sample
3 where rownum NOT IN
4 (select rownum
5 from sample);

FNAME LNAME
---- ----
John Smith
John SmithI can't explain what is happening with your queries exactly, but it is due to the fact that ROWNUM does not behave as you expect.

ROWNUM is a tricky beast, as it is assigned to the records as they are selected. For example, this will NEVER return a row:

select * from mytable where rownum > 1;

It gets the first row from mytable, assigns rownum=1, then checks the condition rownum > 1, which is false.
It gets the second row, assigns rownum = 1 again (since no previous row has been selected yet), then checks the condition rownum > 1, which is false.
etc. etc. etc.

When you have duplicate records, the only way to distinguish them is by the ROWID, which is a physical address:

SQL> select fname, lname, rowid
2 from sample;

FNAME LNAME ROWID
---- ---- ----
John Smith AAA6BDAAFAAABIPAAA
John Smith AAA6BDAAFAAABIPAAB

Now you can:

delete sample where rowid='AAA6BDAAFAAABIPAAB';|||I'm sorry could you please explain the logic here, how is the second row also assigned 1 which leads to the result at the end of n rows as being
1
2
.
n

It gets the first row from mytable, assigns rownum=1, then checks the condition rownum > 1, which is false.
It gets the second row, assigns rownum = 1 again (since no previous row has been selected yet), then checks the condition rownum > 1, which is false.

I appreciate your comment as my problem is solved, just would like to understand rownum.

Cheers.|||ROWNUM applies to the output of the selection process, not the input. So for example if you select any 5 records from a table, they will always have ROWNUM values from 1 to 5, in the order the records were found. If the query has an ORDER BY clause, this is applied after the ROWNUMs have been assigned, hence:

SQL> select dname, rownum from dept;

DNAME ROWNUM
----- ----
ACCOUNTING 1
RESEARCH 2
SALES 3
OPERATIONS 4

SQL> select dname, rownum from dept order by dname;

DNAME ROWNUM
----- ----
ACCOUNTING 1
OPERATIONS 4
RESEARCH 2
SALES 3

SQL> select dname, rownum from dept where dname='SALES';

DNAME ROWNUM
----- ----
SALES 1

SQL> select dname, rownum from dept where dname='ACCOUNTING';

DNAME ROWNUM
----- ----
ACCOUNTING 1

See? If you think of the query processor as a program it looks like this:

-- Select records
ROWNUM = 0
loop
Get next row
If row matches WHERE clause then
ROWNUM = ROWNUM+1
output(ROWNUM) = this row
end if
end loop

ROWNUM In Oracle

Is there any way to implement ROWNUM in Oracle to select row no.
Is there any way to select the row no when retrieving records as
1 Tv
2 Fridge
3 RadioHi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Thanks
Hari
SQL Server MVP
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:3BDAC82C-4AB5-4631-ABAE-AB693B0E1312@.microsoft.com...
> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
There is not a ROWNUM function in SQLServer 2000 using an identity column is
usually the alternative. If you want to rank your values then you could use
a
construct like:
e.g
SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
col1, col2
FROM MyTable M
These links may also help.
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
http://www.microsoft.com/resources/...r />
0761.mspx
http://www.microsoft.com/sql/evalua...pare/oracle.asp
John
"Renjith" wrote:

> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
If it is a big table then the count(*) as inner query will create
performance problem '
"John Bell" wrote:
> Hi
> There is not a ROWNUM function in SQLServer 2000 using an identity column
is
> usually the alternative. If you want to rank your values then you could us
e a
> construct like:
> e.g
> SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
> col1, col2
> FROM MyTable M
> These links may also help.
> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> http://www.microsoft.com/resources/.../>
/c0761.mspx
> http://www.microsoft.com/sql/evalua...pare/oracle.asp
> John
> "Renjith" wrote:
>|||Hi
It may, and indexing would reduce the problem.
You can also do something like:
CREATE TABLE MyTest ( id int not null identity(1,1), val char(1))
INSERT INTO MyTest ( val )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
DELETE FROM MyTest where val = 'C'
SELECT m.id, COUNT(*) as Rank, m.val
FROM MyTest m
JOIN MyTest r ON R.id <= M.id
GROUP BY m.id, M.val
ORDER BY 2
Another alternative would be do deligate the numbering to the client.
John
"Renjith" wrote:
> Hi
> If it is a big table then the count(*) as inner query will create
> performance problem '
> "John Bell" wrote:
>|||Hi
You may want to look at Itzik Ben-Gan's articles in the May 2005 SQL
Server Magazine.
http://www.windowsitpro.com/Article...5828/45828.html
http://www.windowsitpro.com/Article...2302/42302.html
http://www.windowsitpro.com/Article...2646/42646.html
John

ROWNUM function

Does SQL Server 2005 or SQL Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?
please advice!
To select records from row #10 to row #20
Oracle:SELECT *FROM MyTableWHEREROWNUM>9ANDROWNUM<21
MySQL:SELECT *FROM MyTableLIMIT10,20
SQL Server:?
SELECT * FROM MyTable WHERE Row_Number() BETWEEN 10 and 20|||It is not working in SQL Express....why??|||

This one works:

SELECT

OrderID, OrderDate, RowNumberFROM(SELECT OrderID, OrderDate, ROW_NUMBER()OVER(orderby OrderID)as RowNumber

FROM

ORDERS)as tWHERE RowNumberBETWEEN 10 AND 15

Syntax in SQL Server 2005:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

sql

rownum equivalent ?

Hi,
Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?
select rownum from test_table;
Please advise,
Thanks
Samsqlserver has none, the clostest match is to add an identifier-column.

rownum and sub rownum

I have a table with six records. 4 A records and 2 B records.
how do i count them by A and B. when I do a
select rownum, col from table;
i get:
1 A
2 A
3 A
4 A
5 B
6 B

How can I get the following result?
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2

help PleaseWhat DBMS are you on? For Oracle there is the ROW_NUMBER function:

select row_number() over (order by col),
col,
row_number() over (partition by col order by 1)
from table;|||I am on Oracle. This worked. Thank you very much|||Hi, I am unable to understand what this criteria means:

ID = '"& request.querystring("oID") & "'

in the following statement:

select count(distinct hazmatclass) as hzcount
from manifestexp
where orderkey in
(
select orderkey
from manifestexp
where ID = '"& request.querystring("oID") & "'
)
group by hazmatclass;

* manfestexp is a view.
* ID is a column in that view.

Any hints please??|||That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.|||Thank you very much for your feedback.
I am not sure I understand the prepared statement part. How do I go about doing that.

Originally posted by andrewst
That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.

ROWNUM and ORDER BY

Am using Oracle 9i.

Just wanted to know in which order the db will execute my query if my query contains a 'WHERE ROWNUM < 1000' and an 'ORDER BY ...'.
The documentation says that the order of evauation depends upon the indexes used in the ORDER BY, but doesn't specify clearly in which order.

Please help.You can't use ROWNUM and ORDER BY in the same select because the pseudo column ROWNUM is affected before the sort.

So, you have to do :

Select ... FROM (SELECT ... FROM ... ORDER bY...) WHERE ROWNUM<1000

rownum alternate in MS-SQL

I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?This doesn't sound like a good idea... (using rownum)
Please enlighten me with the SQL you used in mySQL?
Also, what version of SQL Server are you using?|||SELECT TOP n

(Being deprecated for modification statements in SS 2008+)
SET ROWCOUNT = n

(SS 2005)
OVER () clause|||I want to get 100 rows from particular record and onward.

You need more than just TOP, don't you?
I don't like the use of rownum - I'm sure there's a better way to get what the OP wants!|||You need more than just TOP, don't you?yes, a WHERE condition, too

oh, and an ORDER BY clause, without which TOP is meaningless

:)|||I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.

I want to get 100 rows onward to one particular data ... how can i ?

ummmmmmmmmmmmmm

what particular row|||what particular rowthe one specified by the WHERE condition|||ummmmmmmmmmmmmm

what particular rowThat row, right there near the middle of my screen.

-PatP|||I'm feeling better now|||"Standard" for SQL 2005:

with cte
as
(select row_number () over (order by name) as num, object_id, name
from master.sys.tables)

select *
from cte
where num >= 4

Rownum

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3....100 along with the each record position.
is it possible without using cursor?
Howdy!see http://www.dbforums.com/t1058224.html|||Assuming that you have atleast one primary key or at least a unique constraint:

Select Count(RowTable.UniqueField) as RowNumber, Mytable.Fields
from Mytable
inner join Mytable RowTable on Mytable.UniqueField >= RowTable.UniqueField

Even I had the Same problem. Thanks To Blindman for his help regarding the query.|||Thanx to r937 and blindman! :)|||Assuming that you have atleast one primary key or at least a unique constraint
Also: NULL values won't be counted. In the other thread the values were used as columnnames, so NULL is quite unlikely. Not sure about myTable.sql

Rownum

I am trying to do a
select count(*) on tablename
where rownum>40000
There are 70000 rows in the table, but my results are 0. How do I get the count above the 40000?
Thanks.row num is alloted to the rows fetched from the query.
if u put the rownum > conditon, no rows will be fetched because it will not satisfy the condition while ROWS are in teh table.

I hope i am clear|||How can I return the rows above 40000? I need to put 70000 rows of data on Excel, but an Excel table holds only 67000 rows.
Thanks.|||Here is a solution,

select *
from table
minus
select *
from table
where rownum < x|||Thank you very much.

row num

can i have the row number in a select like ROWNUM in Oracle DB?
:confused:
I want to to

create procedure sp_table1_sel( @.startRow bigint, @.endRow bintint)
AS
SELECT *
FROM table1
WHER rownum < @.startRow
AND rownum > @.endRow

How can I do that?!

ThanksCheck this SQL TEAM (http://www.sqlteam.com/item.asp?ItemID=1491) link.|||Originally posted by Satya
Check this SQL TEAM (http://www.sqlteam.com/item.asp?ItemID=1491) link.

OK I read something like this in the MSDN mag (may 2003) but if a have 60 000 rows in the table? The query take 30 sec...

something better?!