]]>By: Stew Ashtonhttps://hoopercharles.wordpress.com/2011/07/06/how-many-ways-to-solve-this-sql-problem/#comment-3636
Mon, 11 Jul 2011 06:42:15 +0000http://hoopercharles.wordpress.com/?p=5124#comment-3636Thank you, Charles. This is poetic justice: I have recently pointed out the lack of ORDER BY in other queries, and in my turn I am guilty of disorderly conduct :) Here is a corrected (and perhaps correct) version:select max(C2) C2, SUM(DECODE(PASS,1,DECODE(RN,1,0,C2),-C2)) D from
( select level PASS from DUAL connect by level <= 2 ),
( select rownum RN, C1, C2 from ( select * from T2 order by C1 ) )
group by RN+PASS
having SUM(PASS) != 2
order by max(C1);
]]>By: Charles Hooperhttps://hoopercharles.wordpress.com/2011/07/06/how-many-ways-to-solve-this-sql-problem/#comment-3634
Sun, 10 Jul 2011 16:12:33 +0000http://hoopercharles.wordpress.com/?p=5124#comment-3634I saw your comment and quietly fixed the SQL statement to match your description. The less than and greater than characters tend to get lost because those characters have special meaning in HTML. != is a good substitute for <> or you can use (without spaces):
& lt; (for <)
& gt; (for >)

I am wondering if this part of the SQL statement could cause problems if the data is not added to the table blocks in a completely sequential order possibly due to the amount of free space remaining in the table’s blocks when the new rows are added:

What you posted is an interesting approach to the problem – I believe that your query can be fixed by removing the ROWNUM from “(select rownum RN, T2.* from T2)”, sliding that into an inline view with an ORDER BY clause, and adding ROWNUM outside the inline view. I have NOT attempted this change in your SQL statement, but I suspect that it will work.

Thank you for supplying the link to the AskTom thread. I need to find some time to take a look at that thread.

]]>By: Stew Ashtonhttps://hoopercharles.wordpress.com/2011/07/06/how-many-ways-to-solve-this-sql-problem/#comment-3633
Sun, 10 Jul 2011 15:22:31 +0000http://hoopercharles.wordpress.com/?p=5124#comment-3633…and now miraculously the (not equals) sign has reappeared !?
]]>By: Stew Ashtonhttps://hoopercharles.wordpress.com/2011/07/06/how-many-ways-to-solve-this-sql-problem/#comment-3632
Sun, 10 Jul 2011 13:49:19 +0000http://hoopercharles.wordpress.com/?p=5124#comment-3632Not sure what happened there, should be “having sum(pass) not equal to 2″
]]>By: Stew Ashtonhttps://hoopercharles.wordpress.com/2011/07/06/how-many-ways-to-solve-this-sql-problem/#comment-3631
Sun, 10 Jul 2011 13:47:41 +0000http://hoopercharles.wordpress.com/?p=5124#comment-3631Here is a variation of Chris’ second solution. I use a cartesian join instead of union all (which should save a full scan) and I don’t really pivot since I can just make C2 negative when needed and use SUM.
select max(c2) c2, sum(decode(pass,1,decode(rn,1,0,C2),-C2)) D from
(select level pass from DUAL connect by level <= 2) a,
(select rownum RN, T2.* from T2)
group by RN+pass
having SUM(PASS) <> 2
order by max(C1);

On “when to use the Model clause”, here is a link to a problem where the MODEL clause is the solution I prefer.

Nice job finishing up the starting point that I left as a challenge. The fact that PRIOR can be used in the SELECT clause when using a CONNECT BY clause in the query probably is not well known (I occasionally forget the syntax).

Complicating your second example is the fact that the C1 values in the table have gaps in the sequence – this was intentional so that people could not create a simple self join (outer-join) that specified T1.C1 = T2.C1+1. What you posted is a very good work-around for the lack of gapless sequential values, and is a solution that I had not previously considered as a solution for this problem.

—

Have we listed all of the possible SQL only solutions at this point?

]]>By: Chris Saxonhttps://hoopercharles.wordpress.com/2011/07/06/how-many-ways-to-solve-this-sql-problem/#comment-3627
Fri, 08 Jul 2011 16:46:14 +0000http://hoopercharles.wordpress.com/?p=5124#comment-3627From your starting point Charles, I came up with the following:

It’s also possible to generate the result by unioning the table with itself, but generating row numbers for the two sets that are out of sequence with each-other by one. Then pivoting the results to get the values from each set as separate columns and then subtracting the column values: