Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query - return values from 2 rows as 1 row??
SQL Query - return values from 2 rows as 1 row?? [message #35972] Fri, 26 October 2001 13:52 Go to next message
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


----------------------------------------------------------------------
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 Go to previous messageGo to next message
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

----------------------------------------------------------------------
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 Go to previous message
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.)

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


Current Time: Mon Oct 26 13:40:56 CDT 2020