sql selecting the newest order date

Question

I want to retrieve the newest order and the name of the customer who ordered it:

Select fname, lname, orderdate as newestorder
from customer, cusorder
where customer.cid = cusorder.cid
group by fname, lname
having orderdate = max(orderdate)
;

the error is ORA-00979: not a GROUP BY expression

Answer 1

You're getting the error because you've included orderdate in the select but you're not grouping by it.

That said it won't do what you want. There are many ways to do what you want. Here is one

Select fname, lname, orderdate as newestorder
from customer
    inner join cusorder
     on customer.cid = cusorder.cid

where 
 orderdate = (select max(orderdate) from cusorder)

Answer 2

Try to use join insted point. It is considered good practice. Try this:

Select 

fname, lname , max(orderdate) as newestorder
from customer AS c
INNER JOIN cusorder as cu on c.cid = cu.cid
group by fname, lname
order by newestorder desc
;

Answer 3

should should be able to just do a max date right on the select query assuming the names are the same.

Select fname, lname, max(orderdate) as newestorder
from customer, cusorder
where customer.cid = cusorder.cid
group by fname, lname