I got this SQL question wrong- why ??

[wxman]

With regards to performance and repetative code - the client client questioned the use of the inner join ??

This is what the client sent to me - what is wrong with it or how could it be improved??

select person_key, questions.question_id question_key, question_priority,
max(responses.response_text) response_text,max(responses.created_date) created_date
into #temp_responses
from lp_uat.responses

INNER JOIN lp_uat.questions on responses.question_key = questions.question_id
where(responses.created_date) in (

select max(responses.created_date) from lp_uat.responses

INNER JOIN lp_uat.questions on responses.question_key = questions.question_id
where question_id in (22,23,24,32,2,3)
group by question_id, person_key, question_key )
and question_id in (22,23,24,32,2,3)
group by question_id, person_key, question_priority

[MobileCheese]

At a guess as its ANSI SQL performance wise they might be looking for you to suggest to include the conditions or at least part of in the FROM clause as opposed to the WHERE as this can give some performance gain. In terms of repetative code adding some of conditions into the FROM will also mean you need them only once rather than twice in the WHERE as well.

Not sure if this would run, but may be this,

select
person_key,
questions.question_id question_key,
question_priority,
responses.response_text
response_text,
responses.created_date
created_date
FROM
responses b
INNER JOIN questions on b.question_key = questions.question_id
and question_id in (22,23,24,32,2,3)
WHERE
(b.created_date) = (select max(responses.created_date) from responses
INNER JOIN response.question_key = b.question_id and response.person_key = b.person_id and b.question_priority = question_priority)


You have already restricted to the questions_id's in the INNER join in the from, so from that point onwards you only dealing with those, your sub query joins back to the response table b alias so you dont need to include the question_id in there as you know you have already restricted it to the questions_id you want in the INNER join in the FROM. You then use the sub select to get the max date for each question, by person and tie that back to response table alias b using inner joins and you already know that alias has only the questiions IDs in you want to know about.

I could be wrong, I dont like ANSI had to learn last couple of weeks after years of * and (+), and its always diffcult to just do it on paper.

[Spacecadet]

I dont like ANSI had to learn last couple of weeks after years of * and (+), and its always diffcult to just do it on paper.

Its just a matter of getting used to it, I used to be oracle the switched to SQL Server. Had to do some more oracle recently and definitely prefer the ANSI methods now

[BrowneIssue]

Its just a matter of getting used to it, I used to be oracle the switched to SQL Server. Had to do some more oracle recently and definitely prefer the ANSI methods now

That's what I've always liked about this industry. Standards. We've got so, so many...

[TheFaQQer]

There is a thread somewhere around about someone who failed an interview because he used plus notation rather than the ANSI standard.

I've always used the plus notation, but when I was doing my OCP there were a number of things about using the ANSI standard, so I learnt it then. That said, I still use the plus notation if I need it.

[MobileCheese]

Its just a matter of getting used to it, I used to be oracle the switched to SQL Server. Had to do some more oracle recently and definitely prefer the ANSI methods now

I'm pretty much upto speed with it now, you got to see through the cr*p and use what you need, I can see the benefits in it.

There is a thread somewhere around about someone who failed an interview because he used plus notation rather than the ANSI standard.

That was me I got gig in end, sloppy seconds, turns out company standard is ANSI strickly applied on anything new.

[TheFaQQer]

That was me I got gig in end, sloppy seconds, turns out company standard is ANSI strickly applied on anything new.

It's an interesting thing for companies to decide - do they move to new notation, or stick with the old so that it's consistent with everything else. Four years back, I did a migration (and add-on) for Orange, moving from Oracle 7 to Oracle 9i. Some of the code was so old that it predated the BOOLEAN datatype, so they returned 'TRUE' or 'FALSE' as a text variable.

They decided to stick with the old style, since they wanted consistency. Not sure I'd go that far, though.