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

Row values to string

Hi,
How can I create a string containing all values from a row in a
table(SELECT).
sample:
ID | NAME
--
1 | John -> "1,John"
2 | Alexander -> "2,Alexander"
Thank you,
Roby Eisenbraun MartinsConvert all the column values to a character compatible type and use the
concatenation operator like:
SELECT CAST( id AS VARCHAR ) + ',' +
CAST( name AS VARCHAR ( 30 ) ) + ',' +
..
FROM tbl ;
Anith|||What do you want to do with the string? You can use DTS to export a table or
view to a comma delimited text file.
"Roby Eisenbraun Martins" <RobyEisenbraunMartins@.discussions.microsoft.com>
wrote in message news:BDF4B30A-70E8-4D3C-8D35-7C095D2C167F@.microsoft.com...
> Hi,
> How can I create a string containing all values from a row in a
> table(SELECT).
> sample:
> ID | NAME
> --
> 1 | John -> "1,John"
> 2 | Alexander -> "2,Alexander"
> Thank you,
> Roby Eisenbraun Martins
>sql