0

I am trying to get the newest order for each phone number so I used in oracle

select * from (select * from orders where phonenum ='914780' order by order_date)
where rownum<=1

it works for one number only if I used it of several number it give me wrong results as each number has several orders

mostafa elmadany
  • 139
  • 2
  • 9

1 Answers1

1

Use window function for ordering phone number wise orders and then pick each phone number latest record.

Select * 
from (
  Select o.*,
         row_number() over (partition by phonenum order by order_id desc) AS rowno 
  from order o
  Where phonenum = '1233'
) t
Where t.rowno = 1;

N.B.: use table name and columns according to your DB objects.

MT0
  • 143,790
  • 11
  • 59
  • 117
Rahul Biswas
  • 3,207
  • 2
  • 10
  • 20