CODEWITHSUNDEEP
sqloracle-database
Emile Arab
Emile Arab

Reputation: 61

sql selecting the newest order date

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

Upvotes: 1

Views: 95

Answers (3)

Jande
Jande

Reputation: 1705

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
;

Upvotes: 0

chungtinhlakho
chungtinhlakho

Reputation: 930

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

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

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)

Upvotes: 3

Related Questions