getting latest row

[prodadmin]

select * from order_services

order_ID order_histid parts ordered ordered_date
---------- ---------- ------------- ---------
3899 5 2 12-FEB-08
3899 3 3 11-FEB-08
3898 3 5 10-FEB-08
3898 4 6 12-FEB-08
4200 6 2 10-FEB-08
4200 7 0 12-FEB-08
4201 8 9 14-FEB-08


select * from ( order_ID, order_histid, parts, ordered ordered_date, rank() over (order by parts DESC) as hgh from order_services ) where hgh=1;

order_ID order_histid parts ordered ordered_date RNK
---------- --------- ---------- ---------- ----------
4201 8 9 14-FEB-08


but i am looking for highest parts for each order_id

order_ID order_histid parts ordered ordered_date
-------- ---------- ------------- ---------
3899 3 3 11-FEB-08
3898 4 6 12-FEB-08
4200 6 2 10-FEB-08
4201 8 9 14-FEB-08

i tried with the second query with rank but i only i get highest Part not for each orderid..can any one help

Thanks

[PAVB]

here is a hint... max()

[prodadmin]

you mean this ..select order_ID, order_histid, parts ordered ,max(ordered_date)
from order_services
group by order_ID, order_histid, parts ordered

didn't worked .. i tried with order by des and rownum .. didn't worked

[PAVB]

I didn't mean that, that's horrible.

Here is a second hint, max() is supposed to be used in an inline view -subquery- to help you select the right row from each group.

[prodadmin]

hello
Thanks for your reply but i am not sure what you are saying ..
Do you mind giving me a sample query Please

Thanks

[PAVB]

select  *
from    order_services a
where   a.order_id || a.parts_ordered = (select b.order_id || max(b.parts_ordered)
                                         from   order_services b
                                         where  b.order_id = a.order_id
                                         group by b.order_id)
;

Query returns one row per order_id, the row with the highest number of parts_ordered.

[prodadmin]

is there a significance of this.i believe this made difference

a.order_id || a.parts_ordered

[PAVB]

Oh yes... the whole query made the difference, now it works ;-)

[prodadmin]

"Oh yes... the whole query made the difference, now it works ;-)"

hey
i am not taking away your help and big THANK YOU.All i was asking what is the significance of this ?

a.order_id || a.parts_ordered

[PAVB]

concatenation.
Look at the query, concatenation allows you to compare the two versions of the concatenated columns in a single try.

Test it...
select 'Hello ' from dual;
select 'World' from dual;
select 'Hello ' || 'World' from dual;