PL/SQL :: Rank Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
[code].......

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);

I would like to use the rank function.

Desired update:
A     B     C     D     FLAG
1     2     3     4     
2     4     5     8     
1     2     3     4     D
2     4     5     8     D
7     2     3     4     
9     2     3     4     
7     2     3     4     D
1     2     3     4     D
5     4     5     8     
2     2     3     9     
2     4     5     8     
6     2     3     4     
1     3     3     4     
8     2     8     4     

View 5 Replies


ADVERTISEMENT

PL/SQL :: Any Way To Get Rank Of Students Without Using RANK Function?

Mar 24, 2013

I created a sample table named as "Student" with following data. table contains two columns only - stdid & marks.

stdid     marks     
10          75          
20          60
30          60
40          45
50          30

I have to find the rank of students based on their marks in descending order.Is there a way to get rank without using RANK function?

View 11 Replies View Related

SQL & PL/SQL :: Ranking Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(7,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(9,2,3,4);
[code]....

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);I would like to use the rank function.

Desired update:

A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4

View 3 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records Using Analytical Function

Dec 22, 2012

how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.

My table(tab2) Structure is

DEPTNODEPT_NAMEEMPIDSEXID1
107jadf 1F1
40asdf 55
10purchase 2M2
10sales 3M3
30HR 4F4

I found the Duplicate Record by using the query

with a as
(select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1

how to delete duplicate record .

View 19 Replies View Related

SQL & PL/SQL :: Way When Doing Rank Function In PL/SQL To Pass Field

Jul 13, 2011

there is a way when doing the Rank Function in PL/SQL to pass the field that will be ranked as an override.

SELECT rank(p_ColumnAmt) within group (order by p_ColumnNm desc) rank
INTO v_RnkNoAmt
FROM Table_name
WHERE ??????;

p_Column is the amount I am ranking
p_ColumnNm is the actual field name to Rank.

When I pass the field name with an override I do not get the correct rank back. If I run the Select with the actual field name curr_1_mth_amt, I get the correct rank.

I have about 70 different field to Rank and do not want to make a procedure for each field.

View 7 Replies View Related

SQL & PL/SQL :: Create Rank On Column Without Using Rownum Function

Mar 17, 2013

can we create rank on a particular column without using rownum and rank function.

View 9 Replies View Related

PL/SQL :: Output Like Using Sql Query Without Using Dense-rank Function?

May 1, 2013

Here table - tac has

row1
-----
X
X
X
B

[code]...

I want ouptut like using sql query with out using dense_rank function,

row1 row2

X 1
X 1
X 1
B 2

[code]...

View 7 Replies View Related

SQL & PL/SQL :: See Only Duplicate Records But Not Able To Get Through?

Dec 22, 2010

i am using this query to see only duplicate records but not able to get through

select * from
emp
where rowid NOT IN
( select max(rowid) from emp GROUP BY job_id)

View 7 Replies View Related

SQL & PL/SQL :: Finding Duplicate Records

May 20, 2010

i have table with name, count, flag with dublicate records

example

with swayam name , counts are 3, 4
with ramana name, counts are 5,5
with reddy name, counts are 1,2,3

i want to update the flag

if count are same then update one of record (flag='A') and other should be flag='R'
if count are different then update the max count (flag='A') and other should be reject remaing (flag='R'). use below quires

CREATE TABLE TEST_DUB ( NAME VARCHAR2(99), V_COUNT NUMBER, FLAG VARCHAR2(1));

Insert into TEST_DUB (NAME, V_COUNT)
Values
('SWAYAM', 3);
Insert into TEST_DUB
(NAME, V_COUNT)
Values

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Duplicate Records And Group By

Jun 22, 2010

My duplicate records have been detected by First Name, Last Name, Name, and City.

such as

select FirstName, LastName, Name, City, count(*) as Num of Duplicate from TABLE
GROUP BY FirstName, LastName, Name, City
having count (*) > 1

It gives the duplicate record. Now I need all the columns and the each duplicate record in the select, so I can see why these records are duplicate.

View 6 Replies View Related

PL/SQL :: Deleting Duplicate Records

Dec 20, 2012

I have a table and it's having duplicate records.

for one particular employee, he is having multiple records with the same data in the table

EMPNO    ENAME     JOB     SAL     DOB
-------------------------------------------------------
1               A           X        100     1956
2               B           Y        200     1974
1               A           X        100     1956
3               C           Z        300     1920

[Code]....

like this am having multiple times the duplicates.

I have written the below query to delete the duplicate records. But it is deleting only one record (if we have 5 duplicates it is deleting only 1 ). But I am looking to delete if we have 5 duplicates need to delete 4 duplicates and keep 1 record in the table.

query which am using to delete the duplicates is

DELETE FROM Table1 a 
WHERE ROWID IN (SELECT MAX(ROWID) 
                FROM Table2 b
                WHERE a.ID = b.ID); 

it is deleting only one row but I want to delete 4 records out of 5 and keep one record.

View 12 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records

Sep 29, 2010

say I have duplicate records in TABLE3, can I use following SQL to delete the duplicates?FYI, BUS_FID is unique number column.

TABLE3
------------------------
ori_id*************id***********r_f_a****r_t_a*******bus_id
72840000040572*****740040572****255******267*********1
72840000040572*****740040572****255******267*********2
2840000040572******20040572*****25*******27**********3
7840000040572******70040572*****2********6***********4

DELETE FROM TABLE3 WHERE BUS_ID NOT IN (SELECT MIN(BUS_ID) FROM TABLE3 GROUP BY ORI_ID);

consider TABLE3 has millions of millions of data, say 40 millions.Update, I have tried it and it is very slow...

View 5 Replies View Related

SQL & PL/SQL :: Function To Duplicate The Word

Apr 13, 2012

function to duplicate the word

example member

result will be meber

View 11 Replies View Related

SQL & PL/SQL :: Finding Duplicate Only Records In More Than One Column?

Dec 16, 2010

I am trying to write SQL which finds records which are duplicated in more than one column.

Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...

My Source table:

COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67

Desired Output

COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67

For Table generation

CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)

Insert statements

Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Query To Update Duplicate Records?

Sep 20, 2011

The requirement is I have a table (TAB1), wherein I have 3 columns, ID, LID and STATUS.

The value in ID column = ID_SEQ.NEXTVAL,and LID will be either 0 or 1 and the possible values for STATUS are 'ED','CP', NULL. The ID column is not suppose to have duplicate values, but there is no check on the table for the same.

Someone has updated the existing data and ID column is containing duplicate values. Wherever LID = 0 and STATUS = NULL and if only if ID is duplicated then the ID_SEQ.NEXTVAL has to be assigned to ID field, so that there are no more duplicate values.

CREATE TABLE tab1 (id NUMBER , lid NUMBER, status VARCHAR2(10));

Existing Data
------------------
INSERT INTO tab1 VALUES (1,0, 'ED');
INSERT INTO tab1 VALUES (1,0, 'CP');
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);
INSERT INTO tab1 VALUES (1,0, NULL);

[code]....

get the result using a single update statement.

View 5 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records From A Table

Apr 13, 2011

I need to delete duplicate records from a table (indeed they are multiple duplicates).

Table Data

IDGroupQty
1KK30
1KK0
1KK19
2AA0
2AA30
3AA0
3AA30
3AA30
3AA9

My aim is to delete duplicates out of above data, with the below condtions.

1) first record with value 30 and then with value 0.
2) if there are 3 duplicate records ex: ID is 1 and Group KK, then i have to delete both 30 & 0 qty records.
3) If there are more than 3 duplicate records ex: ID is 3 and Group is AA, the i have to delete all the records with qty value either 30 or 0 and.

I have written a query like below.

SELECT id,
unit,
RANK ()
OVER (PARTITION BY id, unit
ORDER BY id, unit)
num
FROM temp;

with the above query, i am unable to mark this dynamic duplications.

View 10 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records From Table

Aug 30, 2010

How can i delete duplicate records from the table.

View 2 Replies View Related

SQL & PL/SQL :: How To Display Data Without Duplicate Records

Nov 16, 2011

how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.

select distinct comp_id as compid,
comp_disp_name as company,
plcy_id as policyid,

[Code]....

output
-----------------
compid compname policy_id policy_name

19734 Save the Children 9013 GPA
19734 Save the Children 9012 GMC
20097 JMT 9486 GTL
10890 Steelco Gujarat Ltd. 9727 CAR
17330 Golden Jubilee Hotels Limited 8915 CGL
23117 NBHC 9093 GMC
17542 Heinz India 10693 Fire
19821 KSK Fabricators 10341 D&O
3769 Jones Lang Lasalle India 9199 WC
19821 KSK Fabricators 10340 WC

View 10 Replies View Related

Forms :: Restrict Duplicate Records

Jul 18, 2011

there is non database data block of table type.

now i want restrict user that he will not enter same number again.

View 3 Replies View Related

PL/SQL :: How To Count Duplicate Records In A Table

Jun 15, 2012

query to count the duplicate records and to display duplicate records in a table. Iam using oracle 10g.

View 2 Replies View Related

SQL & PL/SQL :: Oracle 9i / 10g - Remove Duplicate Records?

Nov 11, 2010

I have a query which pulls data from emp view

select am_obj_emp_obj(empno,ename,sal,deptno,service) from
(select empno,ename,sal,deptno ,service from emp_vw where 1=1 and rownum < 2000 and service in ('MAN','SACH','SACL','KACL'))
WHERE SAL = (SELECT MAX(SAL) FROM EMPS_VW WHERE DEPTNO = deptno or sal is null)
union all

[code]...

This query is pulling 12 records of which 6 records are coming from the first query and the same 6 records is coming from the second query after union all Here am_obj_emp_obj is the object type and emp_vw is the view

Now I wanted to remove the duplicate records.When I implement the union operater it is giving me error.

ORA-22950: cannot ORDER objects without MAP or ORDER method.

View 5 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Combination Of Records From Table?

Sep 29, 2011

How can I delete the duplicate combination of records from the below table.

CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);

[code]....

The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.

Expected result after deletion

GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26

View 5 Replies View Related

SQL & PL/SQL :: Merge Two Tables Into One And Knock Out Duplicate Records?

Jul 12, 2006

I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.

SQL how can we do this in a simple way? each tables has around millions records.

View 4 Replies View Related

SQL & PL/SQL :: Delete Duplicate Records Without Rowid And Drop

Apr 7, 2012

I want to delete the duplicate records from a table without using Below 2 methods:

1> delete from table_1 a where row_id not in (select min(row_id) from Table_1 b where a.PK=b.PK);

2> Insert into Table_2 select distinct * from Table_1;
Drop table Table_1;
Rename Table_2 to Table_1;

View 20 Replies View Related

JDeveloper, Java & XML :: Duplicate Records After XML Extract

Mar 5, 2011

I've got a table with XML code (CLOB column) and I need to read and write every of parameter(blue marked below) and value (red marked below) record from that.

For example:

create table tmp_mape
(msisdn varchar2(100),
xml_params CLOB )
insert into tmp_mape values
('423903200200',
'<parameters xmlns="http://datalan.sk/webreporting/params/v1_0"><parameter name="id"><value>410</value></parameter><parameter name="isDropped"><value>true</value></parameter></parameters>' );
[code]........

I tried to read this every values of xml code by select below:

select
extractValue(value(x),'/parameter/@name', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') b,
extractValue(value(x),'/parameter/value', 'xmlns="http://datalan.sk/webreporting/params/v1_0"') a,
a.*
[code]......

This select returns a duplicate records because of more the one parameter and value records from XML column.

View 30 Replies View Related

PL/SQL :: Update Duplicate Records In Table With Sequence Value

Mar 25, 2013

My table have duplicate records. I want to impose a primary key on that table. For that I have to replace duplicate values of a column with next value of a sequence.

View 2 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Records Based On Repetition Of Certain Fields

Sep 15, 2011

I have the following situation and need support:

create table try_x
(a number PRIMARY KEY,
b NUMBER,
c NUMBER,
f_text VARCHAR2(10));

insert ALL
into try_x values (0,1,1,'abc')
into try_x values (1,1,1,'abc')
into try_x values (2,1,1,'xyz')
into try_x values (3,1,2,'abc')
into try_x values (4,1,2,'abc')
into try_x values (5,1,2,'abc')
into try_x values (6,1,3,'abc')
into try_x values (7,1,3,'abc1')
into try_x values (8,1,3,'abc2')
into try_x values (9,1,3,'abc2')
select * from DUAL;

Although a is the PK, records with similar b,c,f_text are considered redundant and I need to delete all occurrences in the table where b, c, d are redundant and leave the unique ones. So I need the result to look like:

a b c f_text
-----------------
0 1 1 abc
2 1 1 xyz
3 1 2 abc
6 1 3 abc
7 1 3 abc1
8 1 3 abc2

View 15 Replies View Related

Server Utilities :: SQL Loader - How To Ignore Inserting Duplicate Records

Apr 9, 2010

I'm calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.

Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.

any other way to ignore inserting duplicate records into the tables.

View 7 Replies View Related

Reports & Discoverer :: Report Is Bringing Duplicate Records In Excel

Mar 7, 2011

when i run report in PDF, RTF or HTML then correct numbers of records appear but when i run same report in excel then it bring extra records (duplicate).

View 1 Replies View Related

PL/SQL :: How To Get All Records Using CASE Function

May 20, 2013

first_tbl

First_dt second_dt Item
01-JAN-09     01-JUL-09     item1
01-APR-09     01-DEC-09     item1
01-JUL-09     31-DEC-09     item1
15-DEC-09     10-MAY-10     item1
20-MAR-12     10-AUG-12     item2
31-JUL-12     15-DEC-12     item2
01-APR-09     31-DEC-09     item2

second_tbl

Start_dt end_dt item flag
01-AUG-09     01-NOV-09 item1 null     
02-NOV-09     01-DEC-09     item1     null
01-DEC-09     15-DEC-09     item1     null
15-DEC-09     31-DEC-09 item1 null
01-JAN-10     10-MAY-10 item2 null
10-MAY-10     31-DEC-10     item2 null     
01-JAN-12     15-MAR-12     item2 null
20-MAR-12     31-JUL-12     item2 null

I need o/p in such a way that,if first_dt or second_dt in between start_dt and end_dt, i need to make the flag as y(item is the common join) other wise 'N'.

I was able to make the flag as'Y' but not 'N'

Select A.first_Dt, A.second_Dt, B.Start_Dt, B.End_Dt, A.item, B.item,

Case When B.falg is null
Then 'N'
Else 'Y'
End
From firsttbl A,second_tbl B
Where
A.item=B.item

and

((A.first_Dt>=B.Start_Dt And A.first_Dt<=B.End_Dt)          
Or     
(A.second_Dt>=B.Start_Dt And A.second_Dt<= B.End_Dt));

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved