how to select latest or last row in a table

[alidabiri]

hi,
i have 2 tables: CUST and ORDERS
CUST contains cust_num, cust_name, cust_phone
ORDERS contains cust_num, order_date, order_amt
i want to list all the cust_names and their last order date and amount
how would i code this?
thanks.

[debasisdas]

Use join to retrive record from both the tables by joining on CUST_NUM field. then use group by.

[alidabiri]

i know about join. how would the code look like, please?

[alidabiri]

i want to list all the customer names and then latest date and amount for that date for every customer.

[alidabiri]

here's the correct answer that i figured out:
SELECT cu.cust_name,
od.order_amount
FROM customers cu,
orders od
WHERE cu.cust_id = od.cust_id
and od.order_date =
(select max(order_date) from orders od
where od.cust_id = cu.cust_id)

[debasisdas]

I am glad to know that you found the solution before i do that for you. Happy programming.

[darrinallen]

here's the correct answer that i figured out:
SELECT cu.cust_name,
od.order_amount
FROM customers cu,
orders od
WHERE cu.cust_id = od.cust_id
and od.order_date =
(select max(order_date) from orders od
where od.cust_id = cu.cust_id)

[markchicobaby]

here's the correct answer that i figured out:

SELECT cu.cust_name,
            od.order_amount
FROM customers cu, 
         orders od
WHERE cu.cust_id = od.cust_id 
  and od.order_date =
 (select max(order_date) from orders od
        where od.cust_id = cu.cust_id)

That's not a great way to do it, because you've got a subquery. Doing two queries when only one is needed.

You'll find it should perform faster if you do a join and then get the max all in one queery, something like below. It depends on how Oracle optimises it though, performance issues can be fun to play with. In general the following will be better than what you've got above:

select cu.cust_name, od.order_amount, max(order_date) 
  from customers cu
  join  orders od
    on (cu.cust_id = od.cust_id)
group by cu.cust_name, od.order_amount

Having said that, it really does depend on a number of factors which I won't get into for now.... anyway you have some options now ;)

[ishii]

In query, we are selecting the date as max date using max function but suppose if the last inserted row is the date less than the existing dates [it means this is the last record we have inserted in table] & if we want to retrieve this row...then max function would fail.
In this scenario, how to retrieve the last row?

[ishii]

The query would be :
[this is just a sample query]

select empno,sal
from(select a.*, rownum rwnm from(select empno,sal from test 
                                                   order by rownum desc)a
       where rownum = 1)

[biswaranjan111]

To view the last record of a table in oracle:-----

select * from (select regn_dt,rownum r, regn_no from vt_owner) where r >(select max(rownum) -4 from vt_owner);:icon_neutral:

[biswaranjan111]

select * from (select regn_dt,rownum r, regn_no from vt_owner) where r >(select max(rownum) -4 from vt_owner);

[aksclusive]

sql query for retrieving last row particular column value depending upon its ID???

QUERY:
select top 1 [column_name1],[column_name2] from [table_name] where ID=[ID_value] order by index desc

here index is a column name which contain the number of row in the table..
the above Query help you to retrieve last row in case when you don't the index contain value.

[madhuravula]

In my query, we are picking the latest record using lat_upd date column using using max function and its wrking fine but i got another problem like if the last_upd is same for multiple records its picking those 2 records..but i dont want to pick those two records.. i need to pick either one record if the last_upd date is same for multiple records.
Can any one suggest me on this ?

[QVeen72]

Hi,

Combined with lat_upd, You can Select using Order By RowID.. RowID is unique for each row...

Regards
Veena