SQL & PL/SQL :: Pick One Out Of Duplicates?

Jul 19, 2010

I have need, where I need to pick-up the one of the records out of 2, which have all the fields same but one key column? how should I do it?

sample data:

IDnamecreation dateupdate date
121233ATR7/19/2010 15:307/19/2010 15:30
1213344MTR7/19/2010 15:307/19/2010 15:30

For most of the cases, i am picking latest data using update date, but here where dates are same, I am stuck.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Pick Up 3% Of Records Per Day?

Oct 12, 2010

create table test1(x number, y date)

insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');
insert into test1 VALUES (1, '01-OCT-2010');

[code]....

I need an sql which would pick 3% of the records per day

select count(*), y from test1
group by y
21 10/1/2010
14 10/2/2010
6 10/3/2010

So I need 3% of 21 (.63) randomely picked up for 10/1.

similarly 3% of total number of rows from 10/2, 10/3 to be picked up.

View 2 Replies View Related

SQL & PL/SQL :: Pick Up The Data From Table 1

Jul 9, 2010

I have a reqmt that is little complex.

Example: (Real Data)

Table1 (Column Name)
Height
Width
Color

Table2
ID,Height, Width, Color, Remarks
1,2,3,NULL,NULL
2,NULL,NULL,2,NULL
3,1,2,3,4

What I require is pick up the data from table1 and for each row in table1, check the corresponding column to see if null. If null create a counter and add 1 to it.

I would only check for 1 record at a time in table2 (ID is input parameter in pl/sql) In other words, if my ID is 1, my count should be 1 (since COLOr is null in table2 for id=1 and color is a row in table1) if my id is 2, count is 2 (ht and width are NULL and are both separate rows in table1) if id is 3, count=0 since all fields have data even though some of these columsn exists as rows in table1.

View 25 Replies View Related

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

I need to pick a value based on range like if the range is as below

[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]

Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.

View 2 Replies View Related

Forms :: Pick Single Value From Range?

Mar 19, 2012

i have to pick single value from the given range based on value entered by user, for example if user enter the length 2499 it should be considered as 2000 if he enters 2000 its 2000 or he enters 2501 it will return 2500 , i have written a huge if condition for this purpose in my program which looks very cumbersome,is there a simple to do this other than this method.

the code is as below.

[CODE]
IF
( :CSDO_STORE_RECD_LENGTH - SUBSTR(:CSDO_STORE_RECD_LENGTH,(LENGTH(:CSDO_STORE_RECD_LENGTH)-1),LENGTH(:CSDO_STORE_RECD_LENGTH))) =2000
THEN M_RCVD_LENGTH := 2000;
ELSIF

[code].....

View 9 Replies View Related

PL/SQL :: Pick All Continues Range Of Values Into One Bundle

Jul 11, 2013

I got a situation to develop a complex query. Here is the scenario. I have to pick all the continues range of values into one bundle. like in following example, rows having values between 20 to 25 should have one bunch and 27 to 28 should have another as there is no from or to value from 25 to 28. with q1 ( select 20 as Frm, 25 as tto from dualunion allselect 20 as Frm, 21 as tto from dualunion allselect 20 as Frm, 22 as tto from dualunion allselect 20 as Frm, 20 as tto from dualunion allselect 21 as Frm, 22 as tto from dualunion allselect 28 as Frm, 28 as tto from dualunion allselect 27 as Frm, 28 as tto from dual)select * from q1; 

View 10 Replies View Related

Forms :: How To Pick Excel File And Get Data In Column

Jun 18, 2013

How to pick excel data in oracle form 6i. and data show in forms column ?

View 1 Replies View Related

SQL & PL/SQL :: Audit Trail Trigger To Dynamically Pick Columns During DML?

Jun 13, 2010

I want to make a trigger , to store values that are affected during any DML(insert,update,delete).

CREATE OR REPLACE TRIGGER generate_log
BEFORE INSERT OR UPDATE
ON EMPLOYEE
FOR EACH ROW
DECLARE

[code]....

View 8 Replies View Related

Reports & Discoverer :: Pick Latest Record Rank And Count?

Sep 1, 2010

Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this

name semester status year
A 1 enrol 2010
A 2 withdraw 2010
A 3 enrol 2010
B 1 enrol 2010
B 2 withdraw 2010

I want to count their latest status only. It should come up with
Total Enrol - 2
Total Withdrawn - 1

For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work?
Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'

View 3 Replies View Related

Way To Eliminate Duplicates

Jun 11, 2013

I have a table like below:

tableA
aid des
1 concrete
2 wood
3 straw
4 plastic
5 fiber glass
6 other

tableB
bid material
01 1
02 2
03 3
01 2
01 2
02 3
01 5

The result I need when updating another table with this info is:TableC
edw_id bid Requirement
021 1 concrete, wood, fiber glass
032
025
123
521

I do not want :
concrete, concrete, concrete, wood, wood, fiber glass

SO far I am using the following but since I am dealing with hundreds of column that has the same material, when using listagg() from oracle 11.2g, they column width is too wide to fit into the required column.

update eris_data_work e set E.flex37 =
(select
LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
from CODE_CASING_MATERIAL CM, TBLCASING CA
where CM.code=CA.MATERIAL and CA.well_id=E.owner_oid AND CM.DES IS NOT NULL
GROUP BY CA.well_id)
where E.source='WWIS_ON'

I have even used the regexp_count() to try to eliminate duplicates however I have had no success so far

View 4 Replies View Related

Self Joins Getting Rid Of Duplicates?

Jun 17, 2008

I have a table1:
userid, name, town.

Now i want to do a self join like:

select a.name, b.name, a.town
from table1 a
inner join table1 b
on a.town = b.town
where a.first_name <> b.first_name AND
a.last_name <> b.last_name

i added the where clause to limit duplicates i would get but i still get duplicates eg. A B london, B A london etc.

View 9 Replies View Related

SQL & PL/SQL :: Best Way To Find Duplicates?

Feb 14, 2012

I have to load 50 Million records in a table. I also need to ensure that no duplicate records enter. Business demand is that It may send 2 Million records today, 1 Million records tomorrow, 3 million records on the next day and so on...

I have loaded 6 Million records into the table, but onwards speed of loading records (with duplicate check) is decreasing.

View 16 Replies View Related

PL/SQL :: Removing Duplicates Except One

Sep 19, 2013

db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.

View 5 Replies View Related

SQL & PL/SQL :: How To Remove Duplicates From Collection

Mar 24, 2011

remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.

Object creation

create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');

create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;

Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;

If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error

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

View 4 Replies View Related

SQL & PL/SQL :: Remove String Duplicates

Oct 11, 2013

Removing duplicates from a string that contains years and "-".

Example: 1988-1997-2000-2013-1998-1965-1997-1899

I know this can be done in regular expressions but have no experience in this subject.

select REGEXP_REPLACE(.....) from dual;

View 16 Replies View Related

SQL & PL/SQL :: Remove Repeating Duplicates

Aug 3, 2011

i have two tables

Quote:
Table has following data.

id indicator
---------------
1 A
2 A
3 A

Quote:
Table2 has

id indicator
---------------
1 X
1 X
2 X
3 X

I would like to have following output ( Am running query on Toad)

t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X

View 4 Replies View Related

SQL & PL/SQL :: Get A Specific Record In Duplicates

Aug 17, 2012

I have a requirement with source table has duplicates so i need to select a row based upon the below condition.

Source table

EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30

2 TOM 500 1 40
2 SUNG 1500 1 50

Desired Output

EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50

I tried with MAX and DENSE_RANK but its not getting me the first row.

View 8 Replies View Related

PL/SQL :: Inner Join Resulting In Many To Many Duplicates

Oct 9, 2013

I ran an Inner Join but it returned many, many duplicates. One Answer from a different thread:Because you may be having improper Join condition what has a One to many or a Many to many relationship between the tables. We do not have the data, and hence cannot comment over it. What do I look for to identify an improper join?

View 2 Replies View Related

PL/SQL :: Get A Specific Record In Duplicates?

Aug 17, 2012

I have a requirement with source table has duplicates so i need to select a row based upon the a condition.

Source table

EMP_NO ENAME SALARY CODE EMP_ID

1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50

Desired Output

EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50

I tried with MAX and DENSE_RANK but its not getting me the first row.

View 6 Replies View Related

PL/SQL :: Remove Duplicates From A Column

Nov 8, 2012

I want to remove duplicates from a column MAIN_TABLE based on TLEVEL Column:

create table UNIQ_TEMP
( TLEVEL NUMBER(10,0),
TABLE_NAME VARCHAR2(30),
MAIN_TABLE VARCHAR2(30)
);
[code]....

My Requirement is:

MAIN_TABLE= MARKETING_OPTIN
EXISTS IN MAX(TLEVEL)
REMOVE DUPLICATE MARKETING_OPTIN FROM OTHER LEVELS

This should apply to all the values in column MAIN_TABLE

View 12 Replies View Related

Flexible Handling Of Duplicates Using A Trigger

Feb 12, 2007

I'd like to achieve the following (and YES, I do know that this is not multi-user safe, but that's not the point here):

Before inserting a record, the trigger shall check if there's already a duplicate one. Duplicate means in this case when there is an intersection of the time frame, defined by two numeric timestamps. That's also the cause why I cannot use a simple UNIQUE constraint here (in my opinion).

Okay, that already works (see code below). But now I need colliding records to be written to a temporary table so that those records can be returned and presented to the user for selection.

create or replace
TRIGGER TRIGGER1
BEFORE INSERT ON FLIGHT_TABLE
FOR EACH ROW
BEGIN
FOR fs_entry IN (SELECT * FROM FLIGHT_TABLE)
LOOP

[Code] .........

View 1 Replies View Related

SQL & PL/SQL :: How To Make Return All Records Except Duplicates

Mar 29, 2010

I have a table like this basic example:

ID Product Color Time-In
1 Apple Green May
2 Apple Red April
3 Pear Green May
4 Pear Green April
5 Plum Blue June

In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds

So I should get these

1 Apple Green May
2 Apple Red April
3 Pear Green May
5 Plum Blue June

If I do a select distinct then I will only get those fields I test on (product and color), not the rest.

View 1 Replies View Related

SQL & PL/SQL :: Delete Duplicates From Table Without Using Rowid?

Mar 30, 2010

I am trying to delete duplicates from table without using rowid.

here is data:-

create table test(col1 number(3),col2 varchar2(20));
insert into test values(100,'rocky');
insert into test values(100,'rocky');

[Code]....

I know i am perfoming dml on view. IT wont allow me to perform DML on view which contain columns with expression. IS there any way to delete duplicates without using rowid?

View 36 Replies View Related

SQL & PL/SQL :: Get Distinct / First Record From Jungle Of Duplicates?

Aug 30, 2010

I am trying to work with query to get distinct unique records, following is my scenerio:

DocNo |Title| Revs|UpdateTime|OBID
DOC-101|DESCRIPTION1|1|2/28/2004 11:37|6108-9
DOC-101|DESCRIPTION1|1|2/28/2004 11:36|6106-13
DOC-201|DESCRIPTION2|0|4/24/2005 16:44|7899-1

[code]...

From the above duplicates I would like to get only 1 distinct record based on latest update time (when using distinct on a sub-query since OBID is unique again returning all recds)

I am expecting results like:
DocNo|Title| Revs|UpdateTime|OBID
DOC-101|DESCRIPTION1|1|2/28/2004 11:37|6108-9
DOC-201|DESCRIPTION2|0|4/24/2005 16:47|7900-1
DOC-301|DESCRIPTION3|3|2/21/2007 7:26|6869-4
DOC-304|DESCRIPTION4|3|8/22/2007 9:31| 39208-1

View 3 Replies View Related

SQL & PL/SQL :: To Remove Duplicates From Concatenate String

May 12, 2011

I have a query like

SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......

In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.

View 2 Replies View Related

SQL & PL/SQL :: Commit Interval When Removing Duplicates

May 31, 2012

I am trying to remove duplicates from a table with over 10million records. Below query is working fine but it doesnt contain any COMMIT interval. I have to commit after every 20k or 30k records deletion for which IF loop is necessary.

Query:

delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);

View 9 Replies View Related

Primary Key For A Column Consisting Duplicates

May 28, 2013

I have created a table and i have a column consisting of 1000 records (but where i have duplicates). And now i want to create a primary key for the column.

View 7 Replies View Related

PL/SQL :: How To Eliminate Duplicates From Record Types

Aug 21, 2013

How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."

error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000));  TYPE  ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1  ln_x_type; gc_stmt     varchar2(4000); Begin   gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions;    EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1;  ln_dist_x1:=      ln_x1 MULTISET UNION DISTINCT ln_x1; End; 

I need ln_dist_x1 to have distinct records from table.

View 12 Replies View Related

SQL & PL/SQL :: Retrieve 3 Records From Invoice_data_cur - Getting Duplicates

Oct 8, 2010

I have to write a file using the UTL_FILE that needs to look like the one below. I am getting the righ results but I am getting duplicates, If I have more than one records retrieve in the Invoice_data_cur cursor(for the same pidm) I got the right results, but I got duplicate data, in another words if I retrieve 3 records from the Invoice_ data_cur, I got the same record write to the file 3 times, but the right results. If I only retrieve 1 record in Invoice_ data_ cur , I only get the results of the in invoice_detail_charges_cur I don't get the results of the invoice_ detail_ payments_ cur:

H xxxxxxxxxxxx
I 00399999 10/02/201000000000005000000000000500Jane Smith
D 00366899 Current charges Fall 201010/02/2010Study Abroad Fee - Semester 0000000001200
D 00366899Current charges Fall 201010/02/2010France School Abroad 0000000011200
[code]....

I am getting data in all the cursors..

View 25 Replies View Related

Query Return Results That Contain Duplicates - Only First Or Last Required

May 27, 2008

I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:

select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA
where PLLA.po_line_id = PLA.po_line_id
and PLLA.attribute4 is not null

So my output is something like this:

RCE12 This is an item for AUL1
RCE13 This is an item for PWEILL
RCE14 This is an item for AUL1

I just want either the RCE12 or RCE14 record and not both since they both have the same description.

View 2 Replies View Related







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