How to remove duplicate queries in oracle

Oracle deduplication query method is:

oracle database multi-field deduplication

Method introduction: distinct keyword , group by , row_number ()over (partition by column order by column desc)

My requirement is: query all the information after deduplication based on certain columns. In the end I chose the third method.

My idea: I want to find a simpler way to implement deduplication queries. The more direct the better.

Table structure &&content

##1. Usage of distinct keyword: The field combination after distinct keyword must be deduplicated. distinct must

select distinct id from test

Result; Deduplication based on id

select distinct id, name from test

Result: Deduplication based on the combination of id and name (similar to id | | name This way to remove duplicates)

2. Group by group to remove duplicates

select id, name from test group by id, name

Result: Deduplication based on id, name combination

##3, row_number ()over(partition by column order by column asc | desc) method

3.1 row_number() over(order by column asc) First sort the columns in ascending order, and then return a sequence number for each record

3.2 row_number() over(partition by column1 order by column2 asc) First group by column1 , and then sort the grouped data in ascending order according to column2

Note: order by must have

Example select a.*,row_number() over(partition by a.id,a.sex order by name) su from test a;

Example of deduplication: Deduplication based on id and sex

select id,name,sex from(

select a.*,row_number() over(partition by a.id,a.sex order by name) su from test a )

where su=1

Result:

#My requirement is to query all the information after deduplication based on certain columns.

Recommended tutorial: "

Oracle Tutorial

"

The above is the detailed content of How to remove duplicate queries in oracle. For more information, please follow other related articles on the PHP Chinese website!