Job Application Test from Hell

[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]

[KP Lee]

This doesn't look like a question geared to testing your SQL skills, but your skill at defining "real" requirements from something that seems very silly at first. 1. Assume the person asking this, isn't a lune escaped from the asylum. 2. Try to find the underlying order that they are requesting. 3. ASK them what the underlying order is, suggesting a possibility. 4 ASK them the schema information you need in order to write the query. Sort of like: I see these regions are all in South Africa. I'm not that informed about that area, are you ordering this query by population? No? What is the order criteria you are using? Then find out if the criteria is in the table you are querying? Or they could be testing your knowledge of DB design and waiting for you to ask why the H the region and the name are stored in the same table in the first place. Failing all that, just answer the question. There are a bunch of ways to do it. You can throw in a case statement in a batch select and select the two fields and order by the case result, the union all solution would work, creating a temp table and joining with it is a third option.

[James H]

SELECT Region, Contact FROM RegionTable ORDER BY SubString(Region,2,1) DESC, Contact ASC

[James H]

Ha - you beat me

[Mark_Wallace]

It's a ridiculous question. If there's a reason for ordering as in ex.2, then there must be data that allows the order in that or another table (area code, telephone dialing code, or whatever). If such data isn't available, then the correct answer to the question would be to add a column for it. Otherwise, if it's just an arbitrary order for existing data, use an arbitrary solution -- the second letter of each location in reverse alphabetical order, for example -- then stuff it up the questioner's @rse.

I wanna be a eunuchs developer! Pass me a bread knife!

[PhilLeTaxi]

Hi, With this request,

SELECT region, contact FROM `localisation` ORDER BY SUBSTRING(region,2,2) DESC, contact ASC

I obtain :

region contact
Durban John
Durban Mary
Johannesburg Frank
Cape Town Anna
Cape Town Fred
CapeTown Joe

The list is ordered regarding the second letter of the region. To avoid the missing blank in CapeTown, the end of the string is cut. Bye

[Brady Kelly]

It didn't strike me as that It's in order of Region, Contact asc, but it starts at the 2nd region, wrapping around to the first.

[Xapp]

SELECT * FROM t ORDER BY SUBSTRING(REPLACE(Region, ' ', '') FROM 2) DESC, Contact ASC Tricky.

[PhilLeTaxi]

Hi Again, Oups, I didn't see the response of James H. which has found the same solution. Sorry for the noise. Bye

[Narud Shiro]

Guessing that we are using talking of SQL Server, and without using case when, union, or any other thing like them, this is my best: select Region, Contact from Contacts order by replace(Region, ' ', ''), Contact Can you give me a B+ at least, teacher?

[PhilLeTaxi]

Hi, Your request gives this result :

region contact
Cape Town Anna
Cape Town Fred
CapeTown Joe
Durban John
Durban Mary
Johannesburg Frank

This is slightly different from the expected result. Bye

[ghle]

Christian Graus wrote:

OK, so it's sorted by two fields we can't see ( region and email address ) ?

I think he meant that the test was sent to him via e-mail, not that e-mail was a hidden field.:confused: Heck, I'd just order by the hidden Sequence field. :)

Gary

[Member 96]

A job application test that doesn't reflect a real world problem is an utter waste of time and whoever devised this should be kicked in the balls and repeatedly told to "get real".

---

“If you want to build a ship, don't drum up people together to collect wood and don't assign them tasks and work, but rather teach them to long for the endless immensity of the sea” - Antoine de Saint-Exupery

[Kirk Wood]

SELECT 'look for different job' FROM 'places not full of self serving jerks who wish to prove their supposed superiority' Personally, my experience is that places that serve up such a test are full of jerks who think too highly of themselves. They find great pride in their ability to find questions few can answer, and probably can't produce anything worth having anyway. The number of people who think they are great far exceeds the number who really are great.

[Brady Kelly]

Well, the one that did reflect a real world problem, probably the only one, was: Given a list of names, identify duplicate names as well as possible misspellings of the same name. I scrawled something about soundex in my answer. This was a written test taken after hours. I had nobody to raise issues with, but plenty of time to really think things through.