CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
aaaa
aaaa

Reputation: 21

oracle sql question98

I need to display the date most recent order as well as the order id associated with that order but I can't seem to figure out how to only return the unique order id for that that most recent order. Here's what I have so far: 

select  custlastname || ', ' || custfirstname as Contact_Name,
max(orderdate), orderid
from customer inner join custorder
on customer.customerid = custorder.customerid
where state = 'OH' and companyname is not null  
group by custlastname, custfirstname, orderid
order by custlastname, custfirstname;

This returns 10 results, but I only want the most recent order with that specific order ID?

Upvotes: 1

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this using row_number():

select  custlastname || ', ' || custfirstname as Contact_Name, orderid
from customer c inner join
     (select o.*,
             row_number() over (partition by customerid order by orderdate desc) as seqnum
      from custorder o
     ) o
     on c.customerid = o.customerid and seqnum = 1
where state = 'OH' and companyname is not null  ;
order by custlastname, custfirstname;

Upvotes: 2

Related Questions