OraFAQ Forum: SQL & PL/SQL » SQL Query - return values from 2 rows as 1 row??

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query - return values from 2 rows as 1 row??

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

SQL Query - return values from 2 rows as 1 row?? [message #35972]

Fri, 26 October 2001 13:52

Poonamb
Messages: 16
Registered: October 2001

Junior Member

Hello,

I have a table with the following fields .

Release_Dt DATE,
EstEarningsPerShare NUMBER(10,2) not null
.
.
.
etc...

I need to retrieve the EstEarningPerShare for the max(Release_Dt) which is simple. However,
I also need the max-1(Release_Dt) which I have to depict as the PriorEarningPerShare.

This PriorEarningShare is nothing but the value of the max-1 record's EstEarningPerShare.

In the following output, I need to get the below results.
OUTPUT
------
10/24/2001 0.48 .......
10/19/2001 0.51 .......

I NEED TO GET
RESULT
------
10/24/2001 0.48 0.52 ....... wherein 0.52 is not an actual field but somehow has to
be calculated and returned.

How do I do this? I thought of rownum > 3 first and then minus the output from rownum > 2.
But this does not give me the data in one record.

Please let me know of any possibilities.

Thanks in advance!
Regards,
Poonam

----------------------------------------------------------------------

Report message to a moderator

Re: SQL Query - return values from 2 rows as 1 row?? [message #35974 is a reply to message #35972]

Sat, 27 October 2001 07:03

Milan Kumar Barui
Messages: 16
Registered: October 2001

Junior Member

Hi Poonam,
If you are writing query with ronum> number, then it won't work because rownum is the number of rows selected and it will wotk only when you write ruwnum<number or rownum<=number.
In this case, I have not properly understood what is 0.52, but I think this value is 1-EstEarningPerShare you want. In that case, the query will be :
SELECT Release_Dt,EstEarningPerShare||' '||(1-EstEarningPerShare) FROM YOUR_TABLE
WHERE Release_Dt = (SELECT MAX(Release_Dt) FROM YOUR_TABLE);

Pls let me know at m_barui@yahoo.com, if I am wrong. If you don't want this result, then pls. send me a mail with your req.
Milan

----------------------------------------------------------------------

Report message to a moderator

Re: SQL Query - return values from 2 rows as 1 row?? [message #36028 is a reply to message #35974]

Tue, 30 October 2001 16:35

John Donahue
Messages: 1
Registered: October 2001

Junior Member

You can do subqueries for each column value you want. 'select (select x from tab), (select x from tab) from dual;' (subqueries don't only appear in the where clause.)

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	execute immediate issue - updated
Next Topic:	Re: SELECT with variables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:23:49 CST 2024