Job Application Test from Hell
-
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
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 Joe6 records affected.
modified on Wednesday, November 24, 2010 4:03 PM
-
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
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' -
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 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.
-
How do you ensure the required ordering?
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.
-
How do you ensure the required ordering?
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 union2The 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.
-
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 union2The 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.
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]
-
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
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]
-
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.
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.
-
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
-
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]
No it won’t because the missing interval is due to the level of Brady’s BAC rather than the original task conditions. :)
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.
-
No it won’t because the missing interval is due to the level of Brady’s BAC rather than the original task conditions. :)
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.
Haha, no, I suspect it was a typo on their part, but I treated it like I would a landmine. :~
-
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 union2The 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.
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 -
Haha, no, I suspect it was a typo on their part, but I treated it like I would a landmine. :~
What? Hold a protest rally outside the Pentagon? :confused:
-
select region, contact from ( select region, contact, case when region='Johannesburg' then 5 when region='Durban' then 1 else 10 end as Expr1 from TableName ) as Tbl order by Expr1, Contact
TOMZ_KV
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.
-
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
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 ASCThis 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
-
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 Joe6 records affected.
modified on Wednesday, November 24, 2010 4:03 PM
PIEBALDconsult wrote:
Cape Town
According to the OP, one of those should be without a space.
-
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 ASCThis 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
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.
-
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 ASCThis 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
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. :)
-
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.
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 wrote:
Cape Town
According to the OP, one of those should be without a space.
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: