Error in my query. But what error??

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?
John S.Web DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan LouwerensSoftware EngineerCommented:
"rl.quantity" is in the SELECT clause, but not in the GROUP BY clause
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SwapnilSoftware ArchitectCommented:
There is no "ra" alias in from clause for which you mentioned column ra.amount. Might be typo or you missed the table.
2
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.