with cte_a as (select case when A>B then A else B end as T1 case when A
@dhyeypatel1335
Жыл бұрын
select a,b from Reverse_duplicates where a < b union select a,b from Reverse_duplicates where a = 3 and b = 2 union select b,a from Reverse_duplicates where a > b AND a > 3
@mohammadshahbaz3287
9 ай бұрын
My solution - with base as ( select distinct t1.A, t1.B from Reverse_duplicates t1 inner join Reverse_duplicates t2 on t1.A = t2.B and t1.B = t2.A ), base11 as ( select distinct t1.A, t1.B from Reverse_duplicates t1 left join base t2 on t1.A = t2.A and t1.B = t2.B where t2.A is null ) , base2 as( select t1.A, t1.B from Reverse_duplicates t1 inner join Reverse_duplicates t2 on t1.A = t2.B and t1.B = t2.A and t1.A < t2.A ) select * from base11 union select * from base2 ;
@ItJunction4all
9 ай бұрын
Thanks for providing alternate solution. This SQL query looks good to me.
@VinkalGhodasra
2 жыл бұрын
select distinct least(a, b) as a, greatest(a, b) as b from reverse_duplicates
@ItJunction4all
2 жыл бұрын
Thanks Vinkal Ghodasra for posting the solution ! In which database your SQL query will run ?
@TheAbhishekdada
2 жыл бұрын
Can't we avoid 'less than' or 'greater than' code ?
@gagansingh3481
2 жыл бұрын
Reverse the order and group it we can get the output too
@ItJunction4all
2 жыл бұрын
Thanks Gagan for suggesting logic ! It would be great if you can post the working SQL code here.
@shivarajhalageri2513
2 жыл бұрын
We can solve it using self join🙌🙌
@ItJunction4all
2 жыл бұрын
Yes Shivraj, We can solve it using Self join .
@piotrgoral6926
2 жыл бұрын
Hello :) My solution: with x as ( select distinct case when A > B then A else B end as A , case when B > A then A else B end as B from #tym ) select case when A%2 = 0 then B else A end as A , case when A%2 = 1 then B else A end as B from x
@ItJunction4all
2 жыл бұрын
Thanks Piotr for the alternate solution . The solution looks good to me 😍
@vaibhavkamalnigam5636
2 жыл бұрын
MySQL Solution: with cte_ab as ( select a, b from reverse_duplicates union all select b, a from reverse_duplicates ), cte_merge as ( select a, b from cte_ab group by a, b ) select a, b from cte_merge where a < b Output: a b 1 2 2 3 2 4 5 6
@reachrishav
Жыл бұрын
Second cte is not required. Just use distinct clause. with cte_ab as ( select a, b from reverse_duplicates union all select b, a from reverse_duplicates ) select DISTINCT a, b from cte_ab where a < b
@grzegorzko55
2 жыл бұрын
What do you think about this solution: WITH cte AS (SELECT a, b, CASE WHEN a < b THEN a ELSE b END AS test FROM tab_par), cte2 AS (SELECT a, b, test, CASE WHEN a > b THEN a ELSE b END AS TEST2 FROM cte) SELECT DISTINCT test a, test2 b FROM cte2
@ItJunction4all
2 жыл бұрын
Hi Grzegorz ! Thanks for posting SQL query. I just ran your SQL query and found that it is partially correct: ******************* Your SQL output a b 1 2 2 3 2 4 5 6 *********************** Expected Output A B 1 2 3 2 2 4 5 6 ************************ You can find the difference in 3rd row.
@grzegorzko55
2 жыл бұрын
@@ItJunction4all I have fixed my query: WITH cte AS (SELECT a, b, CASE WHEN a < b THEN a ELSE b END AS test FROM tab_par), cte2 AS (SELECT a, b, test, CASE WHEN a > b THEN a ELSE b END AS TEST2 FROM cte), cte3 AS (SELECT test a, test2 b FROM cte2), cte4 AS (SELECT ROWID, a, b, Row_number() over( PARTITION BY b ORDER BY b ) AS rn FROM cte3), cte5 AS (SELECT * FROM cte4 WHERE rn = 1) SELECT * FROM tab_par WHERE ROWID IN (SELECT ROWID FROM cte5)
@ItJunction4all
2 жыл бұрын
@@grzegorzko55 we do not have Rowid in sql server. U wrote this query in oracle ?
@grzegorzko55
2 жыл бұрын
@@ItJunction4all Yes, oracle 18c xe
@sakshinaik05
2 жыл бұрын
how to perform null safe inner join? Table A ______- colA 1 2 1 5 null null tableB colB null 2 5 5 output _________ colA colB 2 2 5 5 5 5 null null null null
@ItJunction4all
2 жыл бұрын
HI Sakhsi ! This question is related to which database ?
Пікірлер: 25