October 15, 2015 at 7:30 am
Koen Verbeeck (10/15/2015)
Toreador (10/15/2015)
The correct answer is that an error occurs, as neither of those tables exists 😉The more correct answer would then be: "It depends". 😛
You are absolutely right. The only problem with this answer is, that it is correct too often :w00t:
October 15, 2015 at 7:51 am
This was removed by the editor as SPAM
October 15, 2015 at 9:06 am
Appreciated this question--I seldom think to use CROSS APPLY in that way. I'd be curious what the execution plans look like for this type of a join (does it need to execute the statement in the parenthesis for each row, causing a lot of table scans, or is it more optimized than that?)... may have to play around with it a bit...
October 15, 2015 at 12:17 pm
I second Koen here, keep'em coming!
😎
October 15, 2015 at 2:44 pm
g.britton (10/15/2015)
The supposedly correct answer "The TOP 3 selling products for all customers who have made a purchase." is ambiguous. As I read it, I would expect only three rows in the result set. While that is possible, it is not likely. I think the author meant:The TOP 3 selling products for each customer who have made a purchase.
+1 on this. There is a big difference between each and all, and the incorrect usage made it difficult to determine what the author was seeking.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2015 at 4:59 pm
Koen Verbeeck (10/15/2015)
BillLudlow (10/15/2015)
I also didn't think any of the answers were correct as the order by was by CustomerId then TotalDue descYes, but since CustomerID is filtered upon there is only one value, so it doesn't matter.
True, but that doesn't mean that 3 rows can be delivered for a customer who's only placed 2 orders, does it? So all the possible answers are wrong. Unless one interprets "an error" as "not what the SQL programmer expected" in which case the last option (the error) is the only correct answer, but even that is rather far-fetched.
And if the column in SalesOrderHeader called salesorderid identifies a product rather than an order the person who devised the schema should be forbidden ever to name any column again.
Tom
October 15, 2015 at 5:25 pm
TomThomson (10/15/2015)
And if the column in SalesOrderHeader called salesorderid identifies a product rather than an order the person who devised the schema should be forbidden ever to name any column again.
+1 on this! I spent a lot of time thinking about this question because none of the answers seemed correct. The wording around product definitely threw me off. A good conceptual question though.
October 15, 2015 at 11:03 pm
Ok, I think I now have the general idea, I got the possible answers WRONG :blush:
What it should have read, as far as I can tell from comments is;
Up to and including the TOP 3 salesorders by salesorderid and totalvalue for each customer who have placed a successful sales order; this would include those that placed 1, 2 or 3 sales orders but exclude those that had not placed a completed salesorder.
Can I thank the community for teaching me to pay more attention in future and "re read the question and possible answers before posting an amended question!!". I feel suitably humbled 🙁 🙁 🙁 🙁 🙁 🙁 🙁 .
One question; is there a character limit on the length of possible answers?
And thinking about it, the questions I generally get asked are in the form of "can you give me the TOP 5 (or whatever) XYZ for all our clients/customers" So should I disregard everyone who does not reach the magic target, if I apply the same logic?
...
October 16, 2015 at 5:37 am
I hope this doesn't discourage you from submitting QOTDs, although I can see how it could. I also hope it doesn't encourage you to write 2-paragraph answers covering all possible contingencies, server state, legal disclosures and references to the phase of the moon in Australia last Tuesday in each answer. 😉
October 16, 2015 at 10:33 am
There's nothing wrong with getting the possible answers wrong, it isn't the end of the world and if we had to spend the effort on them that would be needed to guarantee no mistakes we probably end up with far fewer people willing to provide questions.
Tom
October 20, 2015 at 11:15 pm
What am I missing here?
The CustomerId in the subqueries Select list and Order By has not been qualified with the table alias, so would give an ambiguous column error. So the query would normally return an error whatever the programmer intended.
In this case there is no error, but why as both S. and C. table aliases are used for CustomerID in the subquery.
Fitz
October 21, 2015 at 7:17 am
We actually use CROSS APPLY quite a bit for retrieving addresses.
October 21, 2015 at 11:51 am
Iwas Bornready (10/21/2015)
We actually use CROSS APPLY quite a bit for retrieving addresses.
That is a good call for getting all client contact details, hadn't thought of that one actually so may use that, thanks!
...
October 21, 2015 at 1:41 pm
happygeek (10/21/2015)
Iwas Bornready (10/21/2015)
We actually use CROSS APPLY quite a bit for retrieving addresses.That is a good call for getting all client contact details, hadn't thought of that one actually so may use that, thanks!
The APPLY operator is actually a very powerful technique. I'd recommend you look into it further.
October 21, 2015 at 2:52 pm
Mark Fitzgerald-331224 (10/20/2015)
What am I missing here?The CustomerId in the subqueries Select list and Order By has not been qualified with the table alias, so would give an ambiguous column error. So the query would normally return an error whatever the programmer intended.
In this case there is no error, but why as both S. and C. table aliases are used for CustomerID in the subquery.
Fitz
When multiple tables are joined, and they share column names, SQL Server has no rule to determine what table an unqualified column name applies to. So if there is more than just one potential candidate, it throws an error.
But when queries are nested, in subqueries, derived tables, CTEs or other similar constructions, there is a hieararchy. SQL Server will always start at the current nesting level and look for matches there. If multiple possible matches are found *in the same (sub)query*, an error is thrown. If exatly one match is found, it is used. If no matches are found, SQL Server repeats the same process at the next lower nesting level.
In this case, the subquery has just a single table, so all unqualified columns are assumed to be from that table. (If they exist in that table, of course - otherwise they are searched in the tables used in the main query).
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply