Job Application Test from Hell

Brady Kelly

I'm not looking for an answer here, I found my own, but this is quite a hard question. Given the table from Fig.1, write an SQL Select statement that would re-organize the results to look like Fig.2 Fig. 1

Region

Contact

Cape Town

Fred

CapeTown

Joe

Cape Town

Anna

Durban

John

Durban

Mary

Johannesburg

Frank

Fig. 2

Region

Contact

Durban

John

Durban

Mary

Johannesburg

Frank

Cape Town

Anna

CapeTown

Fred

Cape Town

Joe

leppie

SELECT RANDOM(IDIOT) FROM KEYBOARD ORDER BY WhoKnowsWhat

((λ (x) `(,x ',x)) '(λ (x) `(,x ',x)))

Christian Graus

Is there any sense to that order ?

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.

Single Step Debugger

Brady Kelly wrote:

but this is quite a hard question

You are sarcastic right? "Union All" will do the job pretty easy.

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.

Chris Meech

You're just like my users, you expect me to divine the requirements.

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Slacker007

SELECT * FROM YourTable ORDERBY Durban, Johannesburg, CapeTown INTHATORDER

Christian Graus

Yeah, I expect that's the main thing he was to glean from the question.

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.

realJSOP

First, the data input routine allowed unvalidated data (the "CapeTown" entry). Second, it's apparently only ordered by region, and the name column doesn't have any brearing. In essense, you should have punched the interviewer in the mouth for wasting your time.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

Brady Kelly

Only in that there is a horizontal partitioning, i.e. rows >= Durban versus rows < Durban, and an apparent outer ordering by partition.

Brady Kelly

How do you ensure the required ordering?

wolfbinary

I was thinking the same thing.

That's called seagull management (or sometimes pigeon management)... Fly in, flap your arms and squawk a lot, crap all over everything and fly out again... by _Damian S_

Brady Kelly

It was by email, and solvable. Durban and Cape Town contacts are sorted by name in the requirement. All contacts are sorted only by Region in the input data.

PIEBALDconsult

The correct answer is: "With which database system?" P.S. With Access/Jet via ADO.net:

select * from RegionContact order by len(Region) mod 2,Region,Contact

Region Contact

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

6 records affected.

modified on Wednesday, November 24, 2010 4:03 PM

AspDotNetDev

So here's my guess as to what the sort order is:

Sort by region, ascending, but making the first item group (by region) in that sort the last item. Do not consider whitespace in the sort order.
When two or more of the regions are the same (again, excluding whitespace), sort by contact ascending.

Rather than follow those zany rules, I'd probably just do this:

SELECT 'Durban' AS Region, 'John' AS Contact
UNION
SELECT 'Durban', 'Mary'
UNION
SELECT 'Johannesburg', 'Frank'
UNION
SELECT 'Cape Town', 'Anna'
UNION
SELECT 'CapeTown', 'Fred'
UNION
SELECT 'Cape Town', 'Joe'

[Forum Guidelines]

Andy Brummer

I hope the question was an attempt to see if you would back away from the keyboard and start asking questions. If not, they deserve all the gung ho developers that they get.

Curvature of the Mind

Single Step Debugger

We can’t use “order by” with the union but we could unite every single row separately – ugly but works.

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.

Single Step Debugger

select * from (select * from dbo.RegionTable RT
where RT.Region = 'Durban' order by RT.Contact asc) as union1
union all
select * from (select * from dbo.RegionTable RT
where RT.Region = 'Johannesburg' order by RT.Contact asc) as union3
union all
select * from (select * from dbo.RegionTable RT
where RT.Region = 'Cape Town' order by RT.Contact asc) as union2

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.

Chris Meech

This will fail, since one of the regions has been entered as "CapeTown" instead of "Cape Town". :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Chris Meech

The only obvious ordering sequence is the number of capitalized letters in Region, followed by the Region, followed by the Contact. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Christian Graus

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

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.