-
getting latest row
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
-
-
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
-
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.
-
hello
Thanks for your reply but i am not sure what you are saying ..
Do you mind giving me a sample query Please
Thanks
-
Code:
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.
-
is there a significance of this.i believe this made difference
a.order_id || a.parts_ordered
-
Oh yes... the whole query made the difference, now it works ;-)
-
"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
-
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;