RowNum and rowID in Oracle

--Note: rownum and ROWID only Oracle, other databases are not supported

SELECT * from Scott.dept;
--the result of the query is called the ' result set '

--rownum a sequence produced in a pseudo-column ' result set ', preceded by a result set, followed by a (# ' o′) column
--In the following result set DEPTNO is 2 for the 20 rownum
Select Rownum,deptno,dname,loc from Scott.dept;

--Deptno for 20,30 in the result set below, respectively rownum
Select Rownum,deptno,dname,loc from Scott.dept where Deptno in (20,30);
--rownum=1 1 Data
Select Rownum,deptno,dname,loc from scott.dept where rownum = 1;
--rownum=2 0 Data
Select Rownum,deptno,dname,loc from scott.dept where rownum = 2;
--rownum<3 2 Data
Select Rownum,deptno,dname,loc from Scott.dept where rownum < 3;
--rownum>0 4 Data
Select Rownum,deptno,dname,loc from scott.dept where rownum > 0;
--rownum>1 Normal understanding is 3 data, but the result is not
--without the first pseudo-column, there is no subsequent pseudo-column, and the pseudo-column increments from 1 to a sequential
Select Rownum,deptno,dname,loc from scott.dept where rownum > 1;

/*
The identity of a row is called a field
The identity of the column is called the field name

Oracle's handling of RowNum,
RowNum is a field that is generated when a result set is obtained, which is used to mark the order of results in the result sets.
This field is called a pseudo-sequence, which is a sequence that does not actually exist.
It is characterized by "sequential marking" and "successive increments",
In other words, there may be rownum=2 records only if there are Rownum=1 records.

Assuming that our query conditions pseudo-rownum=2, then in the query out of the first record,
Oracle marks this record rownum to 1, the result is found to be inconsistent with the rownum=2 condition, and the result set is empty.
*/

--in Oracle, with rownum paging, mysql:limit,sql:server top
Select Empno,ename from Scott.emp;

--Query EMP in 第6-10条
--in result set in 6,10
Select Rownum,empno,ename from Scott.emp where rownum in (6,10);
--between result set in 6,7,8,9,10 (Oracle)
--No 1, no query results
Select Rownum,empno,ename from Scott.emp where rownum between 6 and 10;
--1, we can find the results.
Select Rownum,empno,ename from Scott.emp where rownum between 1 and 10;

--Current number of pages 1 display per page 5
--Using sub-queries
--between
SELECT * FROM (
Select RowNum as Tempid,empno,ename
From Scott.emp
) T1
where t1.tempid between 6 and 10;

--in
SELECT * FROM (
Select RowNum as Tempid,empno,ename
From Scott.emp
) T1
where T1.tempid in (6,10);

--Exercise: Extracting 3rd-4th record in Scott.dept
SELECT * FROM (
Select RowNum as Tempid,empno,ename
From Scott.emp
) T1
where T1.tempid between 3 and 4;

/*
Oracle also provides another sequence: rowID
rowID and rownum differ, in general, each row of data corresponds to a rowid, and is fixed and unique.
This is determined when the data in this row is stored in the database. can be interpreted as a memory address in a Java object.
You can use rowID to query records, and ROWID query records are the fastest query method.
(Who can remember the 18-bit length of the rowid character?) )
rowID only changes occur when a table is moved (such as table space changes, data import/export).
*/
--Query the ROWID of Dept table
Select Rowid,deptno,dname,loc from Scott.dept;
--Query the row data of ROWID=AAAMGXAAEAAAAAQAAA
SELECT * from scott.dept where rowid = ' aaamgxaaeaaaaaqaaa ';

--Face Test

--Delete duplicate data
SELECT * from Tb_test;

CREATE TABLE Tb_test (
Name varchar (18),
Age Number
);
Insert into Tb_test (name,age) VALUES (' Tom ', 22);
Insert into Tb_test (name,age) VALUES (' Jack ', 21);
Insert into Tb_test (name,age) VALUES (' Tom ', 22);
Insert into Tb_test (name,age) VALUES (' Alice ', 22);
Insert into Tb_test (name,age) VALUES (' Tom ', 22);
Insert into Tb_test (name,age) VALUES (' Scott ', 18);
Insert into Tb_test (name,age) VALUES (' Scott ', 18);

--Use distinct to filter duplicate fields
SELECT distinct name from Tb_test
/*
Duplicate data: Tom 22[3] Scott 18[2]
1. Delete all duplicate data
2. Delete duplicate data, but keep one (keep the maximum rowid or minimum)
*/
--1
DELETE from Tb_test
--here where Nam in will delete the name obtained in the subquery statement
WHERE name in (
SELECT Name
From Tb_test
GROUP by name
Having COUNT (name) >1
);

--Keep One Piece
--1. By creating temporary tables (DDL statement operations, soon)
--Note: Using distinct this way can only be applied to duplicate data is all columns
--When the field is more (Name,age,sex,address,phone ...), but the judgment repetition is only the name and the age field, what if it's sex? We can't use distinct.
CREATE TABLE tb_tmp as SELECT distinct name,age from Tb_test;
--Clear table record
--Truncation
TRUNCATE TABLE tb_test;
--Plug the data from the temp table back in
Insert into Tb_test (name,age) select Name,age from Tb_tmp;
SELECT * from Tb_tmp;

--View Tom's rowID
Select Rowid,name,age from tb_test where Name= ' Tom ' and age=22;

--Here Max can also use min
--Delete the data grouped by Name,age that are not the most unique ROWID
Delete from Tb_test where rowID isn't in (
--Query Unique ROWID
--according to the largest rowid,name,age grouping, so that the data after grouping is unique, data that is not in the group should be deleted
Select MAX (ROWID) from Tb_test GROUP by Name,age
);

Delete from tb_test where rowID not in
(
Select MIN (Rowin) from Tb_test GROUP by Name,age
);

RowNum and rowID in Oracle