Urgent: How to create this query?

Hi,

I tried the actual SQL below:

select  *
from  tblUTDISPLIST A
where  A.id = ( select max(B.ID) from tblUTDISPLIST B where B.scbfundCode = A.scbfundCode )

but returns error:
Invalid column name

Is me done something wrong?..

where did you try in SQL*Plus ?

Sorry, my bad.

The correct statement is:

select  AssetNo, Currency, BuyPrice, SellPrice, NavPrice from  tblUTDISPLIST A where  A.id = ( select max(B.ID) from tblUTDISPLIST B where B.assetno = A.assetno )  Order By AssetNo, DispDate

However, i actually need to use these values to update the values in other table.

Let say i got another table called tblUTSHOWLIST, which the field SCBFUNDCODE is linked to the AssetNo in TBLDISPLIST, and i need to update the value of Currency, BuyPrice, SellPrice, NavPrice in tblUTSHOWLIST with the values selected from tblUTDISPLIST.

How can i do this?

 

I'm using JSP for querying data..

sql = "Select A.AssetNo, A.Currency, A.BuyPrice, A.SellPrice, A.NavPrice from  tblUTDISPLIST A "+
       "where A.id = "+
       "("+
       "select max(B.ID) from tblUTDISPLIST B where B.assetno = A.assetno "+
       ")";
ps2 = conn.prepareStatement(sql);
rs2 = ps2.executeQuery();

It returns the error: java.sql.SQLException: Invalid column name

But why when i querying it via SQLPlus there is not error occurs? What's wrong with my code?

See also:
https://www.experts-exchange.com/questions/20796491/Why-this-doesnt-work.html

See my comment on your other question:

https://www.experts-exchange.com/questions/20796491/Why-this-doesnt-work.html 

Hi,

The SQL Statement of

select  AssetNo, Currency, BuyPrice, SellPrice, NavPrice from  tblUTDISPLIST A where  A.id = ( select max(B.ID) from tblUTDISPLIST B where B.assetno = A.assetno )  Order By AssetNo, DispDate

is correct, what if i want to get the value of Currency, BuyPrice, SellPrice, NavPrice from tblUTDISPLIST if want to compare with DispDate?

See:
http://www.junk.net/temp/unittrust/ori2.jpg

What i want:
http://www.junk.net/temp/unittrust/result2.jpg

Is that possible to do it in 1 statement?

Please note this is NOT an assignment, if needed more pts will give if i can get a fast solution.

thks

try this

select a.id, a.buyprice,a.sellprice, a.navprice, a.fundcode
FROM (
      select id, buyprice,sellprice, navprice, fundcode
      from table_a ) a ,
        
        ( select max(id) id, fundcode
        from table_a
        group by fundcode
        ) B
where a.id = b.id       
              

Hi dash420,

This is still not working, this select the values from Max(ID), i need to select the Max(DispDate).

Hi dash,

This bring disaster as the query is eating my resources and eventually make my pc resource to low..

My problem is solved, the SQL statement i used is:

Select A.AssetNo, A.Currency, A.BuyPrice, A.SellPrice, A.NavPrice, b.dispdate from tblUTDispList A ,
(
select max(DISPDATE) DISPDATE, assetno
        from  tblUTDispList
        group by assetno
Order By DISPDATE DESC, AssetNo DESC
) b
Where b.DispDate = a.Dispdate And b.AssetNo = a.AssetNo
Order by b.dispdate desc, b.assetno desc

yaah i forgot to put join on AssetNo. That may caused eating the resource.

Wil finalise this question if the statement is tested fully working, do bear with me before i PAQ this questions, thanks dash and rogaut1 to helping me out, thks :-)