So you think you know SQL well , try this SQL Test.
We have a table which stores trains Source and Destination information. And the table sample data looks like below:
|
SOURCE |
DESTINATION |
DISTANCE |
|
NDLS |
LKO |
520 |
|
LKO |
NDLS |
520 |
|
MUM |
BAN |
700 |
|
BAN |
CHE |
450 |
|
BAN |
MUM |
700 |
|
MUM |
LKO |
1200 |
Now the requirement is that we should have only one [SOURCE-DESTINATION / DESTINATION-SOURCE ] pair in the table. If we have repeating pairs like [NDLS-LKO] & [LKO-NDLS] then remove one entry. Similarly, [MUM-BAN] & [BAN-MUM] is repeated so one row should be deleted. The output should look like below where highlighted rows should be removed.
|
SOURCE |
DESTINATION |
DISTANCE |
|
NDLS |
LKO |
520 |
|
LKO |
NDLS |
520 |
|
MUM |
BAN |
700 |
|
BAN |
CHE |
450 |
|
BAN |
MUM |
700 |
|
MUM |
LKO |
1200 |
Now the challenge is to use only one SQL DELETE STATEMENT to get the only required records in the table.
Leave your answer in the comment box below. Answer will be shared this Sunday. Till then leave your guess as comment.
UPDATE:
Check Teradata SQL Test – Answers for the answer.
simple query
delete from table name where distance in (520,700) and destination in ('lko','mum')
DEL
FROM TRN_INF MAIN_TBL
WHERE (source_1,dest,distance)
IN
(SEL source_1,dest,distance
FROM
(SEL a.source_1,A.dest,A.distance,
Row_Number() Over (PARTITION BY a.DISTANCE ORDER BY a.DISTANCE ASC) AS rwm
FROM TRN_INF a
QUALIFY rwm1) REF)
CREATE TABLE TRN_INF
(
SOURCE VARCHAR(20)
,DESTINATION VARCHAR(20)
,DISTANCE INT
);
INSERT INTO TRN_INF VALUES ('MUM','LKO','1200') ;
INSERT INTO TRN_INF VALUES ('BAN','CHE','450') ;
INSERT INTO TRN_INF VALUES ('BAN','MUM','700') ;
INSERT INTO TRN_INF VALUES ('MUM','BAN','700') ;
INSERT INTO TRN_INF VALUES ('LKO','NDSL','520') ;
INSERT INTO TRN_INF VALUES ('NDSL','LKO','520') ;
DEL FROM TRN_INF WHERE (SOURCE,DESTINATION,DISTANCE ) IN (
SEL A.SOURCE,A.DESTINATION,A.DISTANCE FROM
(
SEL A.*, ROW_NUMBER() OVER (PARTITION BY A.DISTANCE ORDER BY A.SOURCE ) SEQN
FROM TRN_INF A
INNER JOIN TRN_INF B
ON TRIM(A.SOURCE) = TRIM(B.DESTINATION)
AND TRIM(A.DESTINATION) = TRIM(B.SOURCE)
QUALIFY SEQN =1 ) A
)
select source,destination,distance from table1
minus
select destination,source,distance from table1;
sel* from table qualify row_number() over (partition by distance order by distance desc)=1
Del from D_BASEL2DB.train x
where (x.source1,x.destination) in
(sel ou.Source1,ou.destination from (sel a.Source1,a.destination,a.distance,row_number() over( partition by a.distance order by a.distance,a.Source1,a.Destination) as rw
from D_BASEL2DB.train a
inner join D_BASEL2DB.train b
on a.source1=b.destination
and a.destination=b.source1) as ou
where rw>1)
Select source,destination,distance from table a
Minus
Select destination, source,distance from table a
This will work..
delete from distance d
where (src,destination) in(
Select src,destination from (
select src,destination,row_number() over (partition by hashrow(src,destination) order by src) rownum
from distance
) A
where rownum=2
)
del from train_info
where (src,dstn) in
(
sel src,dstn from(
sel t1.*,
rank () over (partition by t1.distance order by t1.src) rnk
from train_info T1, train_info T2
where t1.distance = t2.distance
and t1.SRC = t2.DSTN
and t1.DSTN = t2.SRC
qualify rnk = 1
) DT
);
hey can we try it this way
DELETE from table
WHERE
(select (trim(source) || '-' || trim(destination)) as sourcedest from table)
in
(select (trim(destination) || '-' || trim(source)) as destsource from table)
DELETE FROM _sample_tbl a, _sample_tbl b
WHERE
(a.source_nm = b.destination_nm AND b.source_nm = a.destination_nm AND a.distance = b.distance)
AND a.source_nm < b.source_nm;
delete from table where source, destination in (select destination,source from table) , maybe somethin like that ?
We can delete the duplicate record with using rowid. here the query is:
delete * from T_Name A where A.rowid<(select max(rowid) from T_Name B where A.rowid=B.rowid);
why doesnt the below work? Can anyone tell me?
delete from table where (source,destination) in (sel destination,source from table)
It will delete all 4 rows.
del from train tr
where (tr.source,tr.dest,tr.dist)
in
(sel max(t1.source),min(t1.dest),t1.dist
from train t1
inner join
train t2
on t1.dest = t2.source
and t1.source = t2.dest
group by t1.dist
)
Pls execute below 2 queries. This is the only way we can acheive the output.
CREATE MULTISET VOLATILE TABLE VOLATILE_1 AS
(SELECT SOURCE,DESTINATION FROM TRAIN_GUJJA QUALIFY ROW_NUMBER() OVER(PARTITION BY DISTANCE ORDER BY DISTANCE)=1)
WITH DATA ON COMMIT PRESERVE ROWS;
DELETE FROM TRAIN_GUJJA WHERE (SOURCE,DESTINATION) IN
(SELECT SOURCE,DESTINATION FROM VOLATILE_1)
Hey dude ! ans you posted is not working
Simplest way to get the answer of he above query
with cte as(
Select distance,Source,Destination,
row_number() over( partition by distance order by distance,Source,Destination) as New
from Location
)
delete from cte where new > 1
sel a.source, a.destination,a.distance
from
tab a
union
sel b.source, b.destination,b.distance
from
tab b
;
KEEP IT SIMPLE $%@#%(KISS)! Create Unique rows in a Temp. table delete all duplicate rows from Base table. Insert unique rows from Temp table to Base table. When i have a long list of SQL jobs to complete by EOD i go for the KISS every time. But, i would love to see the "only 1" delete statement answer, please do share..!
Create table temp1 as
(
SELECT
a.distance,
max(a.source) AS source,
min(a.destination) AS destination
FROM t1 as a, t1 as b
WHERE a.source = b.destination
AND a.destination = b.source
group by a.distance
) with data
For practical purposes this table more than likely would have a PK and this requirement would be much easier to process.. 🙂
delete t1 from table1 t1 inner join table1 t2 on t1.destination = t2.source
Hey above one is incorrect. I tried to write many queries but could not get the result . please share the answer. today is sunday only…
delete from table1 as s ,table1 as t where s.source=t.destination and s.destination=t.source;