Job Application Test from Hell
-
Haha, i think i found the hidden order... Region are simply ordered by descending their longitude, contacts by the length of their name (descending) then by their fourth letter (ascending). As we know commonly longitudes of theses cities/regions are Cape Town : 18.45° Durban : 30.6° Johannesburg : 27° For the understanding, we suppose we have a table containing longitudes (while in real life we would need -of course- the request to connect to googlemap api to find it) (googlemap find CapeTown and Cape Town same longitude): wtf_region_longitude region longitude Cape Town 18 CapeTown 18 Durban 31 Johannesburg 27 and the table of fig 1: wtf_region_contacts Region Contact Cape Town Fred Cape Town Joe Cape Town Anna Durban John Durban Mary Johannesburg Frank Doing permutation between Joe and Fred, the request is :
select s.region, contact from
(
select c.region,
contact = case contact when 'Joe' then 'Fred' when 'Fred' then 'Joe' else contact end
,contact as ocontact
from wtf_region_contacts c
) s, wtf_region_longitude l
where s.region=l.region
order by longitude desc, LEN(contact) desc, SUBSTRING(contact,4,1) asc--> region contact Durban John Durban Mary Johannesburg Frank Cape Town Anna CapeTown Fred Cape Town Joe Am I right ?
modified on Friday, November 26, 2010 4:30 AM
I think your scenario is actually more likely than the real pattern being sorting by the second char of region.
-
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
-
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
--There is no obvious sort in fig 2l; we have no specific req saying to order using a particular approach...so brute force order it. Obviously this solution wouldn't scale..then again any solution that makes an assumption that there is a scalable way to order it would only be correct by pure luck. Note - pseudocode, might have syntax errors but the basic methodology works select * from fig1 a into #temp01 --dump data into a temp table alter table #temp01 add orderr integer --add a new column update #temp01 set orderr = 1 where region = 'Durban' and Contact = 'John' --populate order info update #temp01 set orderr = 2 where region = 'Durban' and Contact = 'Mary' --populate order info --TODO update orderr for 4 remaining rows select region, contact from #temp01 order by orderr
-
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