Home » SQL & PL/SQL » SQL & PL/SQL » last row from a query result (oracle 10g)
last row from a query result [message #342606] |
Sat, 23 August 2008 01:49 |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
SQL> ED
Wrote file afiedt.buf
1 select ROUND(avgprice, 10) avgprice, SeQ_no
2 from (select b.*,
3 sum(decode(INVXH_code, 'BUY', invxh_TOTAL_QTY, -invxh_TOTAL_QTY)) OVER (ORDER BY INVXH_date) BALQTY,
4 row_number() over (order by invxh_DATE) seq_no
5 from INVS_TXN_HEAD b)
6 -- where INVXH_BROK_code = :INVXH_BROK_code
7 -- AND INVXH_Securit_CODE = :INVXH_Securit_CODE
8 where INVXH_date < to_DATE('25/02/2008','dd/mm/yyyy')
9 model
10 dimension by (seq_no)
11 measures (invxh_DATE
12 , INVXH_code
13 , INVXH_no
14 , invxh_TOTAL_qty
15 , balqty
16 , INVXH_net_AMOUNT_scy netAMT
17 , 0 balamt
18 , 0 balamt2
19 , 0 avgprice
20 , 0 cost)
21 rules (
22 balamt[seq_no is any] = nvl(balamt[cv() - 1], 0) +
23 decode(INVXH_code[cv()], 'BUY', netamt[cv()] , -(balamt[cv() -1] /
24 balqty[cv() -1] * invxh_TOTAL_qty[cv()]) ) ,
25 cost[seq_no is any] = decode(INVXH_code[cv()], 'SEL', balamt[cv() -1] /
26 balqty[cv() -1] * invxh_TOTAL_qty[cv()], 0),
27 avgprice[seq_no is any] = decode(INVXH_code[cv()], 'BUY', balamt[cv()] /
28 balqty[cv()] , avgprice[cv() - 1])
29* )
SQL> /
AVGPRICE SEQ_NO
-------------------- -------------
2.22124210670000 1.000
2.24828175450000 2.000
2.22339370630000 3.000
2.19552193330000 4.000
2.17612187560000 5.000
where should i modify the query to get just the last row no. 5
i tried to change with line no.9 below,,but got just seq_no col. and that also, with the value 6;
i want the query to return " 2.17612187560000 5.000"
SQL> ed
Wrote file afiedt.buf
1 select ROUND(avgprice, 10) avgprice, SeQ_no
2 from (select b.*,
3 sum(decode(INVXH_code, 'BUY', invxh_TOTAL_QTY, -invxh_TOTAL_QTY)) OVER (ORDER BY INVXH_date) BALQTY,
4 row_number() over (order by invxh_DATE) seq_no
5 from INVS_TXN_HEAD b)
6 -- where INVXH_BROK_code = :INVXH_BROK_code
7 -- AND INVXH_Securit_CODE = :INVXH_Securit_CODE
8 -- where INVXH_date < to_DATE('25/02/2008','dd/mm/yyyy')
9 WHERE seq_NO = (select COUNT(*) from INVS_TXN_HEAD)
10 model
11 dimension by (seq_no)
12 measures (invxh_DATE
13 , INVXH_code
14 , INVXH_no
15 , invxh_TOTAL_qty
16 , balqty
17 , INVXH_net_AMOUNT_scy netAMT
18 , 0 balamt
19 , 0 balamt2
20 , 0 avgprice
21 , 0 cost)
22 rules (
23 balamt[seq_no is any] = nvl(balamt[cv() - 1], 0) +
24 decode(INVXH_code[cv()], 'BUY', netamt[cv()] , -(balamt[cv() -1] /
25 balqty[cv() -1] * invxh_TOTAL_qty[cv()]) ) ,
26 cost[seq_no is any] = decode(INVXH_code[cv()], 'SEL', balamt[cv() -1] /
27 balqty[cv() -1] * invxh_TOTAL_qty[cv()], 0),
28 avgprice[seq_no is any] = decode(INVXH_code[cv()], 'BUY', balamt[cv()] /
29 balqty[cv()] , avgprice[cv() - 1])
30* )
SQL> /
AVGPRICE SEQ_NO
-------------------- -------------
6.000
|
|
|
|
Re: last row from a query result [message #342611 is a reply to message #342606] |
Sat, 23 August 2008 02:55 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Just to complete Michel's recommendation, you should firstly order the result of your query (as now it lacks the ORDER BY clause). If you would choose proper order expression, the row with SEQ_NO=5 would be the first row in the resultset (ROWNUM = 1 in the outer query).
|
|
|
Re: last row from a query result [message #342612 is a reply to message #342610] |
Sat, 23 August 2008 03:08 |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
ty so much for your time.
i m in office & dont have 10g loaded. i'll try at home.
the change will be same at line no. 9?
if i try to modify with this;
LAST_VALUE(avgprice) OVER
(ORDER BY invxh_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as avgprice
in the first SQL line no. 1, is going to help..?
actually not thorough with inline and nested SQL's concept of 10g so clearly. will anyone help me with the code line. as to where exactly the change has to happen, in order to fetch just the last row from the query result.
|
|
|
Re: last row from a query result [message #342613 is a reply to message #342606] |
Sat, 23 August 2008 03:23 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> in the first SQL line no. 1, is going to help..?
No, you will still get 5 rows in the resultset as it will not filter out any row.
Also I am afraid that INVXH_DATE is not visible there; SEQ_NO would be correct (but only to show last AVGPRICE 5 times with SEQ_NO from 1 to 5).
> actually not thorough with inline and nested SQL's concept of 10g so clearly.
Do you mean subqueries? Look at lines 2-5. Actually, you use it in the posted query.
|
|
|
Re: last row from a query result [message #342658 is a reply to message #342611] |
Sat, 23 August 2008 14:49 |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
select ROUND(avgprice, 10) avgprice, SeQ_no
from (select b.*,
sum(decode(INVXH_code, 'BUY', invxh_TOTAL_QTY, -invxh_TOTAL_QTY)) OVER (ORDER BY INVXH_date) BALQTY,
row_number() over (order by invxh_DATE [B]desc[/B]) seq_no
from INVS_TXN_HEAD b)
where invxh_BROK_code = 'B00001'
AND INVXH_securit_cODE = 'S00001'
AND INVXH_date < to_DATE('25/02/2008','dd/mm/yyyy')
[B]and ROWNUM = 1[/B]
model
dimension by (seq_no)
measures (invxh_DATE
, INVXH_code
, INVXH_no
, invxh_TOTAL_qty
, balqty
, INVXH_net_AMOUNT_scy netAMT
, 0 balamt
, 0 balamt2
, 0 avgprice
, 0 cost)
rules (
balamt[seq_no is any] = nvl(balamt[cv() - 1], 0) +
decode(INVXH_code[cv()], 'BUY', netamt[cv()] , -(balamt[cv() -1] /
balqty[cv() -1] * invxh_TOTAL_qty[cv()]) ) ,
cost[seq_no is any] = decode(INVXH_code[cv()], 'SEL', balamt[cv() -1] /
balqty[cv() -1] * invxh_TOTAL_qty[cv()], 0),
avgprice[seq_no is any] = decode(INVXH_code[cv()], 'BUY', balamt[cv()] /
balqty[cv()] , avgprice[cv() - 1])
)
/
AVGPRICE SEQ_NO
---------------- -------------
.1269680711 5.000
where as my requirement is ;
2.17612187560000 5.000
from the result
[Updated on: Sat, 23 August 2008 14:55] Report message to a moderator
|
|
|
Re: last row from a query result [message #342662 is a reply to message #342606] |
Sat, 23 August 2008 15:24 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Firstly, ROWNUM should be applied to ordered rows, which the ones returned from the subquery are not.
Secondly, the MODEL clause calculates the results from previous row(s), so it is not possible to filter the rows in this level. Maybe some analytic function could achieve the same result (as the only dimension column is unique), but it would need lot of work with studying the requirements and choosing the proper method.
More simple, you should follow the advicesand use one of the ways described in http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_select_the_TOP_N_rows_from_a_table.3F (the third one would suit best), something like SELECT avgprice, seq_no
FROM (<the original query
ORDER BY seq_no DESC)
WHERE rownum = 1;
|
|
|
Re: last row from a query result [message #342664 is a reply to message #342606] |
Sat, 23 August 2008 16:02 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
I was just puzzled, that although you use subqueries and model clause (which I have never used before, just derived its behaviour from the SQL Reference), you are unable to apply a simple SQL framework.
Seems my puzzle is solved: http://forums.oracle.com/forums/thread.jspa?threadID=696567&tstart=0
For your own good, open the SQL documentation (available at http://tahiti.oracle.com/) and study all frameworks used in this query. Play with them so you will understand their behaviour (as you also were pointed in that thread). Or do you like the dependency on forum help every time you need to make simple adjustments on the proposed query?
|
|
|
Re: last row from a query result [message #342681 is a reply to message #342664] |
Sun, 24 August 2008 01:19 |
rhnshk
Messages: 26 Registered: May 2008
|
Junior Member |
|
|
can you suggest some good easy-to-understand links or articles. where there is step-by-step explanation for beginners.
i want to know the usage of
select ....
from (select .... these nested selects.
what is inline view query, which part is main & why, which section is used finally to display the result..? how do i quickly decide as to how many Selects wiil i have to use in my query?
ty
|
|
|
|
Re: last row from a query result [message #342686 is a reply to message #342606] |
Sun, 24 August 2008 01:54 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
> i want to know the usage of these nested selects.
They are used when they need to be - you want to process the resultset of the query and you are unable (due to SQL limitations) to do it directly in that query.
Do you understand SELECT from a table/view? The subselect is equivalent to select from a view, you just include the view definition directly into the query. Have a look at WITH clause - this may help with the clarity of the code (as you will use its result as a view)
For the next, read the documentation.
|
|
|
Goto Forum:
Current Time: Sat Nov 09 17:20:44 CST 2024
|