OraFAQ Forum: SQL & PL/SQL » Select best 2 value out of 3 three values

Home » SQL & PL/SQL » SQL & PL/SQL » Select best 2 value out of 3 three values (Oracle 11g, Window 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select best 2 value out of 3 three values [message #637314]

Thu, 14 May 2015 16:44

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Dear Experts.
I have one table std having column asg1, asg2, asg3, total.
The column asg1, asg2, asg3 are having values. i want to select two best values from these column values from each row and add them and update the total column.
For example:
if

ASG1       ASG2       ASG3
 3           2          4

in this example value of column ASG1 and ASG3 are best i.e 3 and 4 therefore query should take only these value for this record, add them and store the result of this in Total Column.

Table creation and value insertion is as under:

create table std
(reg_no number,
asg1 number, 
asg2 number, 
asg3 number, 
total number
);

insert into std values(1, 4, 3, 4, null);
insert into std values(2, 2, 3, 4, null);
insert into std values(3, 4, 3, 2, null);
insert into std values(4, 2, 3, 2, null);

REG_NO      ASG1       ASG2       ASG3      TOTAL
1            4          3          4
2            2          3          4
3            4          3          2
4            2          3          2

how can i solve this problem. as i already use Decode, Case and Rank Function but i culd not solve it. please help me in this regards.

Thanks .

Report message to a moderator

Re: Select best 2 value out of 3 three values [message #637322 is a reply to message #637314]

Thu, 14 May 2015 23:50

saipavan.plsql
Messages: 17
Registered: February 2015
Location: chennai

Junior Member

hi this query help's you

update std
   set total =
       (asg1 + asg2 + asg3) - least(asg1, asg2, asg3)
 where reg_no = 1;

Report message to a moderator

Re: Select best 2 value out of 3 three values [message #637323 is a reply to message #637322]

Fri, 15 May 2015 00:44

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or better:

SQL> create table std
  2  (reg_no number,
  3  asg1 number,
  4  asg2 number,
  5  asg3 number,
  6  total number
  7  );

Table created.

SQL> insert into std values(1, 4, 3, 4, null);

1 row created.

SQL> insert into std values(2, 2, 3, 4, null);

1 row created.

SQL> insert into std values(3, 4, 3, 2, null);

1 row created.

SQL> insert into std values(4, 2, 3, 2, null);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table std drop column total;

Table altered.

SQL> alter table std add total as (asg1 + asg2 + asg3 - least(asg1, asg2, asg3));

Table altered.

SQL> select * from std;
    REG_NO       ASG1       ASG2       ASG3      TOTAL
---------- ---------- ---------- ---------- ----------
         1          4          3          4          8
         2          2          3          4          7
         3          4          3          2          7
         4          2          3          2          5

4 rows selected.

Never store a computed column.

Report message to a moderator

Re: Select best 2 value out of 3 three values [message #637324 is a reply to message #637323]

Fri, 15 May 2015 01:35

vippysharma
Messages: 73
Registered: May 2013
Location: www

Member

One More :

create table std (reg_no number,asg1 number,asg2 number,asg3 number,
total NUMBER GENERATED ALWAYS AS (asg1+asg2+asg3 - least(asg1,asg2,asg3)) VIRTUAL);
 
 
insert into std (reg_no,asg1,asg2,asg3) values (1,10,20,30);
insert into std (reg_no,asg1,asg2,asg3) values (2,40,50,60);
insert into std (reg_no,asg1,asg2,asg3) values (3,70,80,90);
 
select * from std;


REG_NO	ASG1	ASG2	ASG3	TOTAL
  1	10	20	30	50
  2	40	50	60	110
  3	70	80	90	170

Report message to a moderator