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
ADVERTISEMENT
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
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
Apr 11, 2013
I have a view that is displaying records that are almost duplicates with the exception on one column.
Distric name id status logtime
------- --------- ---- ------ ---------------
ATLANTA John 007 A 4/5/2011 8:20
ATLANTA John 007 B 4/5/2011 8:20
ATLANTA John 007 D 10/5/2012 8:20
JERSY C. Eric 111 C 8/1/2012 4:45
PLANO Jake 522 A 1/2/2012 7:22
DENVER Steve 222 C 11/24/2012 9:00
DENVER Steve 222 D 11/24/2012 9:00
I have a situation where I need to only display one status per name at a specific time.I would like to return the following:
Distric name id status logtime
------- --------- ---- ------ ---------------
ATLANTA John 007 A 4/5/2011 8:20
ATLANTA John 007 D 10/5/2012 8:20
JERSY C. Eric 111 C 8/1/2012 4:45
PLANO Jake 522 A 1/2/2012 7:22
DENVER Steve 222 C 11/24/2012 9:00
So I decided to include a rank for the different statuses to identify the highest rank status and eliminate the lowest rank status records I don't want.
Select distric,name,id,status,
case status
when 'A' then 1
when 'B' then 2
when 'C' then 3
else 4
end statusrank,
logtime
from tst;
[code]...
I would like to return the record with the highest statusrank when they duplicates with the exception of the status column.Is there a built in function I can use for this ? The statusrank can be returned in query. My final return would be:
Distric name id status statusrank logtime
------- --------- ---- ------ ---------- ---------------
ATLANTA John 007 A 1 4/5/2011 8:20
ATLANTA John 007 D 4 10/5/2012 8:20
JERSY C. Eric 111 C 3 8/1/2012 4:45
PLANO Jake 522 A 1 1/2/2012 7:22
DENVER Steve 222 C 3 11/24/2012 9:00
View 7 Replies
View Related
Feb 8, 2013
I want to learn how to make columns out of records?
View 8 Replies
View Related
Nov 26, 2012
I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
[code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
View 7 Replies
View Related
Jun 17, 2012
Having following table:
UserID REC_TYP REC_CD
12345 'OFFR' 12
23456 'MSG' 13
I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'
View 2 Replies
View Related
Jun 25, 2013
I have a select query select userid from user_tbl where name='hhh';
I want to return 0 when no records are fetched and I want to handle both teh cases in a single query other than proc or function.
View 12 Replies
View Related
Jan 15, 2013
I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
[Code]....
View 7 Replies
View Related
Jun 30, 2012
I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.
-> will exec ctx_ddl.sync_index('index_name')'resolve my problem?
-> since the view is a materialized view, how can i make sure that the latest data added are also picked up?
View 2 Replies
View Related
Oct 17, 2012
In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
View 1 Replies
View Related
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
