Home » SQL & PL/SQL » SQL & PL/SQL » fatch column value on basis condition (PL Sql)
fatch column value on basis condition [message #445145] Fri, 26 February 2010 03:58 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all

i need some help to write SQL query foe a scenario
explained below

Table and data storage format

table structure:

 App_no, bid_1, bid2, bid3, price1, price2, price3



data in table

App_no, bid_1, bid2, bid3, price1, price2, price3
 001    10     15    14      60      62       65
 002    12     14    18      60      68       62



i need to fetch the bid, price, (bid*price)value columns
for the condition where in (bid*price)is max

in given data structure expected result should be

App_no   bid   price   value 
001      15     62      930     --bid2 ans price2 columns
002      18     62      1116    --bid3 ans price2 columns



please revert

Thanks
sonal
Re: fatch column value on basis condition [message #445148 is a reply to message #445145] Fri, 26 February 2010 04:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Use group by

Whats hurting you here ?

Let me re read you post...i guess you are asking for some thing like combinations ?
sriram Smile

[Updated on: Fri, 26 February 2010 04:09]

Report message to a moderator

Re: fatch column value on basis condition [message #445150 is a reply to message #445145] Fri, 26 February 2010 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:

Michel Cadot wrote on Thu, 22 January 2009 07:45
Post a Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).


Please do it.

Regards
Michel

Re: fatch column value on basis condition [message #445152 is a reply to message #445145] Fri, 26 February 2010 04:12 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
i need to query the calculation first & then find max among all the 3 value & then bring the bid and price used to derive that value

like
App	b1  b2	b3  p1	 p2	p3	v1	v2	v3
1	10  15	14  60	 62	65	600	[b]930[/b]	910
2	12  14	18  60	 68	62	720	952	[b]1116[/b]
 



on basis of above data my column get change for 1 st row it were bid2 , price2 and for 2nd row it become bid3 ,price3

same thing to have to do on table contains more than million of rows

thank
sonal
Re: fatch column value on basis condition [message #445157 is a reply to message #445152] Fri, 26 February 2010 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I need a test case.

Regards
Michel
Re: fatch column value on basis condition [message #445161 is a reply to message #445152] Fri, 26 February 2010 04:53 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL>    select app_no, bid_1*price_1,bid_1*price_2,bid_1*price_3,
  2    bid_2*price_1,bid_2*price_2,bid_2*price_3,
  3    bid_3*price_1,bid_3*price_2,bid_3*price_3
  4    from data
  5   /

    APP_NO BID_1*PRICE_1 BID_1*PRICE_2 BID_1*PRICE_3 BID_2*PRICE_1 BID_2*PRICE_2
---------- ------------- ------------- ------------- ------------- -------------
BID_2*PRICE_3 BID_3*PRICE_1 BID_3*PRICE_2 BID_3*PRICE_3
------------- ------------- ------------- -------------
         1           600           620           650           900           930
          975           840           868           910

         2           720           816           744           840           952
          868          1080          1224          1116


SQL>


why BID_2*PRICE_2, bid_3*price_3... what about BID_2*PRICE_3, BID_3*PRICE_2 which is 1224



sriram Smile
Re: fatch column value on basis condition [message #445172 is a reply to message #445145] Fri, 26 February 2010 05:38 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Dear sriram

bid & Price column is co related to each other
thats why i need to multiple them in Sets of Bid1*price1
Bid2*price2 & Bid3*price3

& my issue is related to finding max of set wise value column that is

Bid1*price1 = value1
Bid2*price2 = value2
Bid3*price3 = value3

and the corresponding bid & price column to derive that value column


Thanks
Sonal
Re: fatch column value on basis condition [message #445173 is a reply to message #445172] Fri, 26 February 2010 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With a test case as requested, I could easily show you how to do it.

Regards
Michel
Re: fatch column value on basis condition [message #445174 is a reply to message #445145] Fri, 26 February 2010 05:41 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
test case i had given it in form of example ,please tell me what else detail will help you??
Re: fatch column value on basis condition [message #445175 is a reply to message #445174] Fri, 26 February 2010 05:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A CREATE TABLE statement and a set of INSERT statements that together create the test data you are using.

We can do it ourselves, and sometimes do, but in general, if you want our help, it would be nice if you did some of the work....
Re: fatch column value on basis condition [message #445177 is a reply to message #445145] Fri, 26 February 2010 05:43 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
if
Bid1*price1 = value1
Bid2*price2 = value2
Bid3*price3 = value3
and
value1 = value2 = value3

for example
10*30 = 12*25 = 15*20

what i must doing here? what select?


Re: fatch column value on basis condition [message #445182 is a reply to message #445145] Fri, 26 February 2010 05:58 Go to previous message
ora_baby
Messages: 89
Registered: May 2008
Member
try
with ora_test as 
(
select 1 App,10 b1, 15 b2, 14 b3, 60 p1, 62 p2, 65 p3 from dual 
union all
select 2 App, 12 b1, 14 b, 18 b3, 60 p1, 68	p2,62 p3 from dual
)
select tmp.app, tmp.b, tmp.p, tmp.val
from 
(
select t.*, t.b1*t.p1 pr1, t.b2*t.p2 pr2, t.b3*t.p3 pr3
, case when (t.b1*t.p1 > t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then b1 
    when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then b2
    when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 < t.b3*t.p3) then b3   
  end as b 
, case when (t.b1*t.p1 > t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then p1 
    when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then p2
    when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 < t.b3*t.p3) then p3   
  end as p
, case when (t.b1*t.p1 > t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then t.b1*t.p1 
    when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 > t.b3*t.p3) then t.b2*t.p2
    when (t.b1*t.p1 < t.b2*t.p2) and (t.b2*t.p2 < t.b3*t.p3) then t.b3*t.p3  
  end as val
  from ora_test t
) tmp
Previous Topic: Stored procedures variable arguments
Next Topic: Analytic Functions and Ranking/Ordering
Goto Forum:
  


Current Time: Sat Oct 01 05:56:44 CDT 2016

Total time taken to generate the page: 0.11009 seconds