Home » SQL & PL/SQL » SQL & PL/SQL » Columns replicate (Oracle 12c)
Columns replicate [message #686519] Fri, 30 September 2022 19:28 Go to next message
rajivn786
Messages: 159
Registered: January 2010
Senior Member
Hi,

I have a simple requirement, but not sure


 create table a (id1 number, id2 number, val1 varchar2(100), val2 varchar2(100));
 
 Insert into a (1,2,'ABC','DEF')
 Insert into a (2,1,'ABC','DEF');
 Insert into a (3,4,'ght','jkl')
 Insert into a (4,3,'ght','jkl');
 
 commit;
 
 output : 
 1 2 ABC DEF
 3 4 ght jkl


Since id1 and id2 are just the replicas of each other, I need to select 1 row out of the 2 rows.


Re: Columns replicate [message #686520 is a reply to message #686519] Sat, 01 October 2022 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL>  create table a (id1 number, id2 number, val1 varchar2(100), val2 varchar2(100));

Table created.

SQL>
SQL>  Insert into a (1,2,'ABC','DEF');
 Insert into a (1,2,'ABC','DEF')
                *
ERROR at line 1:
ORA-00928: missing SELECT keyword
Please test your test case before posting it.

SQL> select distinct least(id1,id2) id1, greatest(id1,id2) id2, val1, val2 from a;
       ID1        ID2 VAL1       VAL2
---------- ---------- ---------- ----------
         3          4 ght        jkl
         1          2 ABC        DEF

2 rows selected.
Re: Columns replicate [message #686521 is a reply to message #686520] Sat, 01 October 2022 09:55 Go to previous message
rajivn786
Messages: 159
Registered: January 2010
Senior Member
Thank you and I will test it.
Sorry I replaced some text and "values" was one of them and even that went away.
Previous Topic: Procedure Parameter List
Next Topic: trigger executing a procedure to execute dbms_stats.gather_table_stats
Goto Forum:
  


Current Time: Thu Dec 08 20:46:55 CST 2022