Deduplication and sorting in Oracle

Today, my friend asked a particularly tangled question: in a database table, we need to repeat a field in the table and sort the remaining data by another field,

Today, my friend asked a particularly tangled question: in a database table, we need to repeat a field in the table and sort the remaining data by another field,

Today, my friend asked a particularly tangled question:

In a table in the database, the table duplicates a field in the table and sorts the remaining data according to another field,

Create a table as follows:

Create table TEST_DISTINCT (
ID integer not null,
NAME varchar (20) not null
);

Insert into TEST_DISTINCT values (0, 'A ');
Insert into TEST_DISTINCT values (1, 'bb ');
Insert into TEST_DISTINCT values (2, 'cc ');
Insert into TEST_DISTINCT values (3, 'dd ');
Insert into TEST_DISTINCT values (4, 'ee ');
Insert into TEST_DISTINCT values (5, 'A ');
Insert into TEST_DISTINCT values (6, 'cc ');

The DISTINCT keyword comes to mind as soon as you see the deduplication:

Select distinct name from test_distinct;

Unfortunately, it cannot be associated with the field id, so du Niang saw the most popular method:

Select *, count (distinct name) from table group by name;

Select *, count (distinct ename) from emp group by name;

Unfortunately, an error is reported directly during the operation, so the error is changed:

Select job, ename, count (distinct ename) from emp group by job, ename;

The error was reported, but it still didn't work. After studying the problem for an hour, I still couldn't solve it. I don't know if it was because of the version. In short, I feel that DU Niang is still unreliable!

Then I found the solution:

To remove one line of data, you must remove the first line or the second line. In this case, something like a computer won't be selected, so you have to choose

My method is:

Select max (id) as mt, name from (select * from test_distinct where id> 2) group by name order by mt asc;

If there are other fields:

Select * from emp where emp. mgr in (

Select mm from (
Select max (mgr) as mm, ename from (select * from emp where empno> 7521) group by ename order by mm asc
)

Nn );

After the solution, we found that it was so simple that we saw the de-duplication moving towards the misunderstanding of distinct,

In addition, this article is well-designed and cannot be used to make mistakes!