Job Application Test from Hell

[Brady Kelly]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. :) Here is mine, a little more general but very much the same:

select first.*, 0 outerSeq from (select top(select COUNT(*) from Contacts) * from Contacts where Region >= 'Durban' order by Region, Contact) first
union all
select second.*, 1 outerSeq from (select top (select COUNT(*) from Contacts) * from Contacts where Region < 'Durban' order by Region, Contact) as second
order by outerSeq

[PIEBALDconsult]

What? Hold a protest rally outside the Pentagon? :confused:

[Single Step Debugger]

This is shorter than my solution, I like it!:thumbsup:

The narrow specialist in the broad sense of the word is a complete idiot in the narrow sense of the word. Advertise here – minimum three posts per day are guaranteed.

[Pete OHanlon]

Well, one way to do this would be to do something along the lines of:

SELECT Region, Contact FROM WhatACrappyTest
ORDER BY SUBSTRING(Region,2,1) DESC, Contact ASC

This works based on the fact that the second character is ordered descending, and the contact orders ascending. This even takes the fact that your have CapeTown and Cape Town in the Region column. Obviously, the interviewer should be challenged on the validity of this question.

I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Onyx

[AspDotNetDev]

PIEBALDconsult wrote:

Cape Town

According to the OP, one of those should be without a space.

[Forum Guidelines]

[Christian Graus]

If I was the interviewer, I'd have hired you based on that answer.

Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.

[AspDotNetDev]

Vader (Star Wars Gangsta Rap):

Impressive, now release your anger; can't you sense that your friends are in danger?

The impressiveness of your solution and you being who you are reminded me of that quote. :)

[Forum Guidelines]

[PIEBALDconsult]

Sure, but which one? Fred or Joe? I performed some data cleanup, so sue me. :-D Edit: WTF?! How'd it wind up attached to the wrong post? Sorry, just playing through...

modified on Wednesday, November 24, 2010 5:44 PM

[PIEBALDconsult]

Sure, but which one? Fred or Joe? I performed some data cleanup, so sue me. :-D (Now lets see if it stays where it's supposed to be.) :mad:

[PIEBALDconsult]

Pete O'Hanlon wrote:

even takes the fact that your have CapeTown and Cape Town

Have you tested that?

[AspDotNetDev]

He's only looking at the second character. In the case of "CapeTown", that'd be "a". In the case of "Cape Town", that'd be "a". What's there to test?

[Forum Guidelines]

[AspDotNetDev]

PIEBALDconsult wrote:

Sure, but which one? Fred or Joe?

I hadn't noticed that.

[Forum Guidelines]

[AspDotNetDev]

With your above observation that "CapeTown" is first attached to Joe and then to Fred, your message makes more sense. I think I like this interview question... all kinds of details to help root out those who don't pay very good attention (I am apparently one of them). :)

[Forum Guidelines]

[PIEBALDconsult]

And the number of characters in an entry.

[NickHighIQ]

First thing's first, the order. It appears to be grouped by Region, the groups are ordered by MIN(Contact) (assuming MIN/MAX works in the way I expect, i.e. SELECT MAX('a', 'z') would return 'z' - never had to do an aggregate over varchar fields, thank GOD) and then ordered by Contact ascending. Thought process: Durban, JBurg, Cape Town - no obvious ordering there, but they're grouped... John, Mary - alphabetical Anna, Fred, Joe - alphabetical John, Frank, Anna - alphabetical (desc), so the earliest name in the alphabet in each region is used to order the regions... So, here's the SQL (SQL Server 2008):

SELECT
Region, Contact
FROM
TheStupidestTableEver
ORDER BY
MIN(Contact) OVER(PARTITION BY Region) desc, Contact

Results:

Region Contact

Durban John
Durban Mary
Johannesburg Frank
Cape Town Anna
Cape Town Fred
Cape Town Joe

So, do I win a prize? ;P In fact, I don't need one, that was a satisfying problem to solve :-D

[Serguei]

SELECT Region, Contact
FROM Fig1
ORDER BY
CASE Region WHEN 'Durban' THEN 1 ELSE 2 END,
Contact

Tip, you can do:

SELECT TOP 100 PERCENT * FROM Fig1

Even better tip: Don't do that - your query doesn't guarantee (although will more than likely result in) the correct ordering. You should have sorted by outerSeq, Contact in the outer query. See http://blogs.msdn.com/b/queryoptteam/archive/2006/03/24/560396.aspx

[Michael Kingsford Gray]

Won't work. The middle "Cape Town" has the blank missing, and is "CapeTown" This makes it a very hard problem.

[Brady Kelly]

Thanks, I especially like the top 100 percent

[altncsab]

This is my solution. Because there was no pre-condition then anything is allowed :-D

declare @T_MyTable Table(Region varchar(200), Contact varchar(200))

select case when Contact = 'Fred' and Region like 'Cape%Town' then 'CapeTown'
when Region like 'Cape%Town' then 'Cape Town'
else Region end Region,
Contact
from @T_MyTable
order by substring(Region,2,1) desc, Contact

[Simon_Duckett]

SELECT [Region], [Contact] FROM Table1 ORDER BY SUBSTRING(REVERSE([Region]), 2, 1), [Contact]