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 Go to next message
rhnshk
Messages: 20
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 #342610 is a reply to message #342606] Sat, 23 August 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enclose your previous query in an inline view and select the last row in the outer query.

Regards
Michel
Re: last row from a query result [message #342611 is a reply to message #342606] Sat, 23 August 2008 02:55 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
rhnshk
Messages: 20
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
rhnshk
Messages: 20
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
rhnshk
Messages: 20
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 #342684 is a reply to message #342681] Sun, 24 August 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
Chapter 9 SQL Queries and Subqueries

Regards
Michel
Re: last row from a query result [message #342686 is a reply to message #342606] Sun, 24 August 2008 01:54 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: having doubt with USING clause in JOINS
Next Topic: dynamic decode
Goto Forum:
  


Current Time: Sat Dec 03 18:31:32 CST 2016

Total time taken to generate the page: 0.13481 seconds