Challenges !!!
TSQL Challenge
Last month I answered about a SQL Challenge from Antoine Gemis (french here). My solution wasn't very nice, I used a flaw in the data to provide an easy query with basic outer joining.
As the only challenger, I won it !
Antoine's told be about some regular TSQL Challenges (meaning SQL Server) but i didn't check until two days ago, and TSQL Challenge 8 was just published.
I sent my proposal, which is :
(
SELECT
Emp.EmpID,
Emp.EmpName,
0,
Emp.EmpName,
cast(0 AS float(53))
FROM
@Employees AS Emp
/* The next filter is to not show employee who have no one reporting to them
where
exists (select null from @Employees as Emp2
where Emp2.ReportsTo = Emp.EmpID)
*/
UNION ALL
SELECT
E.EmpID,
E.EmpName,
C.Lvl + 1,
C.InitName,
cast(C.Ord + (E.EmpID * POWER(100.0000, -1*(C.Lvl +1))) AS float(53))
FROM
@Employees AS E
INNER JOIN CTEmp AS C
ON E.ReportsTo = C.EmpID
)
SELECT SPACE(Lvl*4) + EmpName AS [Managed BY]
FROM CTEmp
WHERE InitName = @manager
ORDER BY Ord ASC;
I like how clear the final select is and the alias used to name the CTE !
I used Oracle's way for lvl (similar to pseudo column level) and InitName (similar to connect_by_root) for the later filtering, and did quick and dirty maths to order the siblings.
With Oracle's connect by the answer is mostly "as is" :
FROM Employees
WHERE CONNECT_BY_ROOT EmpName = :manager
CONNECT BY PRIOR EmpID = ReportsTo
ORDER SIBLINGS BY EmpId ASC;
NoCoug SQL Challenge
I think every one can learn a lot from those Challenges, that's why i like them.
My last "wow" thing is about NoCoug's one.
Chen Shapira wrote a summary about this challenge.
In the comments i saw a trackback (or pingback, whatever it is) leading to André Aurajo's solution.
Everyone who read the question knows - being mentionned in the statement - that the classic way to solve the problem is to self cross joins "n" times the dice table.
André says something very logic, very simple and which turns to be very effective : any integer can be decomposed as a sum of k*2ⁿ (k being 0 or 1).
With precalculated probabilities for dice², (dice²)², ((dice²)²)² and a very smart use of bitand, he achieves with excellent performance and readable SQL.
Just read his post, it's fully explained.
I don't know where nor when I'll reuse that algorithm, but I will !
This entry is filed under SQLing. And tagged with bitand, Challenges, Connect By, cte, Hierarchy, NoCOUG, TSQL. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.
Very good idea. I'm anger to see how TSQL and PL/SQL will perform on those challenges. Thanks Waldar !