Error in my query. But what error??

John S.
John S. used Ask the Experts™
on
This query will fail.  Assume all table names, column references, and implied data types are correct.

Can you tell me why? This is a question a recruiter asked in a technical screen. He said I could research it and re-submit my answer.

SELECT Max(r.facility) AS max, 
       Row_number() 
         OVER( 
           partition BY r.contact 
           ORDER BY r.contact ), 
       ra.amount, 
       rl.quantity, 
       rl.item_number 
FROM   request r 
       JOIN request_line rl 
         ON rl.request_id = r.id 
GROUP  BY r.contact, 
          ra.amount, 
          rl.item_number; 

Open in new window


Is it a simple sytnax error I am missing, or something more?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
"rl.quantity" is in the SELECT clause, but not in the GROUP BY clause
SwapnilSoftware Architect
Top Expert 2005

Commented:
There is no "ra" alias in from clause for which you mentioned column ra.amount. Might be typo or you missed the table.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Besides the previous good inputs, I'm not sure the need of having the MAX() and GROUP BY statements since the ROW_NUMBER function will return distinct numbers making the GROUP BY useless.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial