Multi Column Sort

  • john.arnott (4/9/2009)


    Peso,

    I'm not sure that I'm following your toll-booth analogy properly. I don't see how it fits with the data you posted as coming from the OP:

    SELECT 1, 2.35, 3.01, 3.49, 4.25, 4.79, 5.36, 5.82, 6.31

    UNION SELECT 2, null, 3.59, 4.32, null, 5.21, null, null, 7.12

    UNION SELECT 3, NULL, null, null, 5.23, 5.41, 6.33, 6.89, 7.99

    UNION SELECT 4, null, 5.46, 5.31, 5.92, null, 6.87, 7.34, null

    UNION SELECT 5, 5.12, 5.66, 6.31, 6.92, null, 7.87, 8.34, null

    In the example above you start with record 1 which has 8 toll-booths open. So you need to get next record which has the minimum difference in fees. As you can see, that is record 2 which as a minimum difference of 0.42 over toll-booth (passage) 5.

    Then you find next record which is #3 which has a minimum difference of 0.2 over toll-booth 5 (again).

    Then you find next record which is #4 which has a minimum difference of 0.45 over toll-booth 7.

    Then you find next record which is #5 which has a minimum difference of 0.2 over toll-booth 2

    And as you can imagine, the solution has to be a hideous RBAR-solution, with 0.5 * n^2 iterations.


    N 56°04'39.16"
    E 12°55'05.25"

  • Nice explanation, Peso.

    Of course, in this example you are assuming that the first record is easy to identify, but even that is not necessarily so.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • It doesn't matter where you start.

    You can, as you drive "downwards" the records, also drive "upwards" the records.

    I am thinking of an algorithm that picks an arbitrary record in the middle somewhere of totalt records, sorted by minimum value across all columns. That should give a good starting point.

    Then you find the nearest "downwards" record AND the nearest "upwards" record in same SELECT with MIN(ABS()) functions.

    Then you iterate, for the nearest "upwards" record that record's nearest upwards record, and the nearest "downwards" record for the recently picked nearest "downwards" record.

    And so on, until there are no more records to pick in either direction...

    Or, just pick an arbitrary record in the sample data and work your way upwards until no more records can be found.

    Then you start working your way downwards from the original picked record, until there are no more records to pick.


    N 56°04'39.16"
    E 12°55'05.25"

  • RBarryYoung (4/10/2009)


    Nice explanation, Peso.

    Of course, in this example you are assuming that the first record is easy to identify, but even that is not necessarily so.

    It sounds very promising, Peso...bear in mind that, near the end of the sample data as ordered by CorrectSequence, the difference in fees between two rows of col1 is much less than 1: the difference in fees for col8 is several orders of magnitude higher. col1 and col2 on the same row remain within an order of magnitude of each other, col7 and col8 require scientific notation to display.

    If you try to identify the first record by the most obvious method, matching on min of each column

    i.e. MIN(col1) = col1 OR MIN(col2) = col2

    you get something like 7 rows returned!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Maybe this example will be clearer. Suppose you are doing an experiment with rats and mazes. You have a maze that a rat can find a single direct route through. You place fifteen buttons throughout just off the direct path so the rat has to remember where they are and deviate from the path a little to punch the button to get a reward, then continue through the maze without getting lost while making all the detours for snacks. So you put a rat into the start of the maze and mark the time when each button is pushed. Every minute you start a new rat off into the maze. (The rats have a letter assigned but it's not really relevant to the problem.) For each rat going in you record a new trip through the maze and you record the time and number of each button when pushed. Some rats will randomly miss a button here and there. In this case we'll assume it takes a rat 30 to 54 seconds between button locations. We also assume none of the rats catch up and pass the rat in front of it before completing the maze. So if each row in the table represents a trip through the maze, the ordered rows will increment from left to right and columns will increment from top to bottom and there will be random nulls.

    You could also think of a train schedule as a good example. You have several trains running a route with 15 stops along the way. One train after another and they don't pass each other. Because of varying passenger load throughout the day, all of the trains don't always stop at every station. If you are looking at a table of the train schedule, you would have times that always increment from left to right and increment top to bottom with empty spots where the trains don't stop.

    I've included a script to build a table for the rats. Two tables record each trip for a rat and the time and button number for each button pushed. Then the two tables are used to construct a schedule showing all the data. For simplicity, the times are stored as integer values representing seconds from midnight. As you can see, if by chance all the rats always missed one of the buttons (it was too hard to find) then that column would not be represented in the final output. So you wouldn't have a complete column of nulls. We can also assume in our rat experiment that one researcher with a clipboard is marking time for each rat in the maze. When their rat is done, they grab another rat and start it into the maze and follow it through. If there are several rats in the maze at a time and each researcher, one at a time, then records the data they collected, the data wouldn't be entered in the correct order. So I also rigged the script so the table doesn't output data already in the correct sorted order. And again, while the rat letter may prove useful in ordering the table, the goal is to find a way to sort the data based only on the columns of numbers. It's also very simple to increase the size of the sample dataset as much as you want.

    So again (and I'm sure someone will say I'm completely changing the requirements), the data should be sorted using only the values in the 'b' columns without relying on the letters or id field. The values increment left to right in each row (which they will always do anyway) and should increment from top to bottom in each column. Nulls are not considered in the sorting but simply fall where they may.

    CREATE TABLE #trips (TripID INT, Rat CHAR(1))

    CREATE TABLE #buttons (TripID INT, ButtonNumber INT, PunchTime INT)

    CREATE TABLE #results(TripID INT, Rat CHAR(1))

    DECLARE @i INT, @j-2 INT, @char INT, @start INT,

    @time INT, @tripID INT, @sql NVARCHAR(500)

    SET @i = 0

    SET @tripID = 0

    SET @start = 28800

    WHILE @i < 20

    BEGIN

    SET @char = 65

    WHILE @char < 73

    BEGIN

    SET @tripID = @tripID + 1

    INSERT INTO #trips VALUES(@tripID, CHAR(@char))

    SET @time = @start

    SET @char = @char + 1

    SET @j-2 = 0

    WHILE @j-2 < 15

    BEGIN

    SET @j-2 = @j-2 + 1

    SET @time = @time + CAST(((RAND() * 100) / 4) + 30 AS INT)

    IF(CAST(RAND() * 10 + 1 AS INT) <> 5)

    INSERT INTO #buttons VALUES(@tripID, @j-2, @time)

    END

    SET @start = @start + 60

    END

    SET @i = @i + 1

    END

    INSERT INTO #results

    SELECT TripID, Rat FROM #trips

    DECLARE buttons CURSOR FOR

    SELECT DISTINCT ButtonNumber

    FROM #buttons ORDER BY ButtonNumber

    OPEN buttons

    FETCH NEXT FROM buttons INTO @i

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    SET @sql = N'ALTER TABLE #results ADD b' + CONVERT(NVARCHAR(2), @i) + ' INT'

    EXEC sp_executesql @sql

    SET @sql = N'UPDATE #results SET b' + CONVERT(NVARCHAR(2), @i) +

    ' = b.PunchTime FROM #results r INNER JOIN #buttons b

    ON r.TripID = b.TripID AND b.ButtonNumber = ' + CONVERT(NVARCHAR(2), @i)

    EXEC sp_executesql @sql

    FETCH NEXT FROM buttons INTO @i

    END

    CLOSE buttons

    DEALLOCATE buttons;

    -- Comment out this section to see the table in correct order --

    WITH b AS (

    SELECT ROW_NUMBER() OVER(ORDER BY Rat, b1) AS RowNum, TripID

    FROM #results )

    UPDATE #results SET TripID = b.RowNum

    FROM #results a INNER JOIN b

    ON a.TripID = b.TripID

    -- ********************************************************** --

    SELECT * FROM #results ORDER BY TripID

    DROP TABLE #trips

    DROP TABLE #buttons

    DROP TABLE #results

  • jsanborn (4/10/2009)


    Maybe this example will be clearer. Suppose you are doing an experiment with rats and mazes. You have a maze that a rat can find a single direct route through. You place fifteen buttons throughout just off the direct path so the rat has to remember where they are and deviate from the path a little to punch the button to get a reward, then continue through the maze without getting lost while making all the detours for snacks. So you put a rat into the start of the maze and mark the time when each button is pushed. Every minute you start a new rat off into the maze. (The rats have a letter assigned but it's not really relevant to the problem.) For each rat going in you record a new trip through the maze and you record the time and number of each button when pushed. Some rats will randomly miss a button here and there. In this case we'll assume it takes a rat 30 to 54 seconds between button locations. We also assume none of the rats catch up and pass the rat in front of it before completing the maze.

    The data generator that I provided also works in this model.

    Except that my rats are all so drunk or high that they can only find one or two of the buttons at best (though they can always find at least one of the buttons that the rat in front of them found). Plus there are some serious relativistic time dilation effects in my maze. :-D.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Attached is a zip-file with working solutions for original problem.

    Both files contains same algorithm.

    One file is based on Barry's test data and one file is based on jsanborn's latest sample data.

    For Barry's sample data, 500 records takes about 2 seconds, 1000 records about 6 seconds and 1500 records takes about 15 seconds.


    N 56°04'39.16"
    E 12°55'05.25"

  • jsanborn (4/10/2009)


    Maybe this example will be clearer. ...

    I've included a script to build a table for the rats. ...

    So again (and I'm sure someone will say I'm completely changing the requirements), the data should be sorted using only the values in the 'b' columns without relying on the letters or id field. The values increment left to right in each row (which they will always do anyway) and should increment from top to bottom in each column. Nulls are not considered in the sorting but simply fall where they may.

    Unfortunately, the data generated by this is flawed - it breaks the rules of the challenge...

    See this sample :

    TripID Rat b1 b2 b3 b4 b5 b6 b7 b8 b9 b10 b11 b12 b13 b14 b15

    62 F 32514 32554 32608 32659 32698 32749 32780 32833 32880 32915 32947 32990 33032 33080 33126

    63 G 32550 32597 32630 32666 32706 32739 32785 32830 32864 32915 32948 NULL 33036 NULL 33107

    Columns b6, b8,b9 and b15 are all breaking the rules (or at least it is impossible to order these rows correctly)

    MM

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Unfortunately, the data generated by this is flawed - it breaks the rules of the challenge...

    With the time spans as close together as they were, I thought that might be a possibility. Since they are random numbers, of course, it's a different set of data every time. I executed it several times and looked it over to see if all the numbers were falling in order and from what I could see it seemed to be working out OK. I suppose by adding a little more spread in the start times for each row should fix it.

  • I guess everyone's happy with the suggestion posted 4/10/2009 8:44 PM?


    N 56°04'39.16"
    E 12°55'05.25"

  • Peso (4/13/2009)


    I guess everyone's happy with the suggestion posted 4/10/2009 8:44 PM?

    Peso, really interesting take on it, but it doesn't seem to work on my test data - unless I have invalid test data -but I don't think so.

    Try this with my own generated data (I have re-posted your solution with my data below), which is sorted already so that you can see the correct result easily...rows 80 and 81 are swapped by your solution - but this is possible in this data set - not an error, however row 90 gets placed after row 87 instead of after row 89...this is an error.

    I am really impressed by your code and would love to see if you can get round this problem because it is super fast.

    I have a version that works using recursive cte, but it is woeful in terms of speed and can only cope with a limited number of rows at the moment because the recursion gets out of hand when the rowcount increases.

    I hope to find a way to fine tune it, but if you can get yours to work I can give up on mine.

    I might look at adopting part of your method (UNPIVOT - can't believe I have never spotted that before!) to see if I can speed things up a bit.

    SET NOCOUNT ON

    CREATE TABLE#Result

    (

    RowID INT IDENTITY(1, 1),

    ID INT PRIMARY KEY CLUSTERED

    )

    DECLARE@ID INT

    CREATE TABLE#Stage

    (

    ID INT,

    theCol CHAR(4),

    theValue FLOAT,

    minValue FLOAT

    )

    CREATE NONCLUSTERED INDEX IX_Stage ON #Stage (theCol, ID) INCLUDE (theValue)

    ;with data(id,col1,col2,col3,col4,col5,col6,col7,col8)

    as (

    select 1, 24, NULL, 121, 138, NULL, 276, 338, 404 UNION ALL

    select 2, 487, NULL, 521, NULL, 567, 614, 712, NULL UNION ALL

    select 3, 828, NULL, NULL, 1014, 1110, 1180, 1200, NULL UNION ALL

    select 4, 1381, 1421, 1487, 1498, 1502, NULL, 1587, NULL UNION ALL

    select 5, NULL, 1746, NULL, 1823, NULL, 1921, 1986, NULL UNION ALL

    select 6, NULL, 2118, 2185, 2219, 2288, 2387, NULL, NULL UNION ALL

    select 7, 2468, 2552, 2608, 2674, 2766, 2810, 2851, NULL UNION ALL

    select 8, 2973, NULL, 3138, NULL, 3323, 3339, NULL, NULL UNION ALL

    select 9, NULL, NULL, 3728, NULL, NULL, NULL, 3785, NULL UNION ALL

    select 10, NULL, 3991, 4026, NULL, 4107, 4203, 4280, 4283 UNION ALL

    select 11, 4347, NULL, NULL, NULL, 4471, NULL, 4503, NULL UNION ALL

    select 12, 4547, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL

    select 13, 5036, 5077, 5158, 5174, NULL, 5266, NULL, 5432 UNION ALL

    select 14, 5529, 5588, 5595, 5690, 5748, NULL, NULL, 5894 UNION ALL

    select 15, 5926, 5998, NULL, 6048, NULL, NULL, NULL, 6234 UNION ALL

    select 16, 6313, 6374, 6389, NULL, NULL, 6533, 6606, 6639 UNION ALL

    select 17, 6728, 6795, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL

    select 18, 7170, NULL, 7270, 7358, NULL, 7477, 7574, 7653 UNION ALL

    select 19, 7674, NULL, NULL, NULL, 7770, NULL, 7905, 7965 UNION ALL

    select 20, NULL, NULL, NULL, NULL, NULL, 8261, NULL, NULL UNION ALL

    select 21, 8495, NULL, NULL, NULL, 8725, NULL, 8841, 8851 UNION ALL

    select 22, 8894, NULL, 8987, 8991, 9072, NULL, NULL, 9265 UNION ALL

    select 23, 9270, NULL, 9359, NULL, NULL, 9549, NULL, 9694 UNION ALL

    select 24, NULL, 9807, NULL, NULL, 9930, 9997, 10018, NULL UNION ALL

    select 25, NULL, NULL, NULL, 10345, NULL, 10412, NULL, 10560 UNION ALL

    select 26, 10588, NULL, NULL, 10808, NULL, NULL, 10934, 10997 UNION ALL

    select 27, 11069, 11118, NULL, 11160, NULL, 11265, NULL, NULL UNION ALL

    select 28, 11373, NULL, NULL, 11550, NULL, NULL, NULL, NULL UNION ALL

    select 29, 11795, 11820, NULL, 12007, 12022, 12070, 12070, 12163 UNION ALL

    select 30, NULL, NULL, NULL, NULL, NULL, 12444, NULL, 12542 UNION ALL

    select 31, 12553, 12624, NULL, 12724, NULL, 12834, NULL, 12934 UNION ALL

    select 32, 12982, NULL, 13106, 13173, NULL, 13276, 13336, NULL UNION ALL

    select 33, NULL, NULL, 13545, 13546, 13630, NULL, 13793, NULL UNION ALL

    select 34, 13886, NULL, 13969, 14062, NULL, NULL, NULL, 14321 UNION ALL

    select 35, 14343, 14368, 14374, 14452, 14496, 14583, 14613, NULL UNION ALL

    select 36, 14717, NULL, 14795, 14797, NULL, NULL, 14904, 14931 UNION ALL

    select 37, NULL, NULL, 15086, NULL, 15176, NULL, NULL, 15310 UNION ALL

    select 38, NULL, 15408, NULL, 15477, 15489, NULL, NULL, 15706 UNION ALL

    select 39, 15719, NULL, NULL, 15829, NULL, 15876, NULL, NULL UNION ALL

    select 40, 16034, NULL, 16081, 16170, 16243, 16245, 16317, NULL UNION ALL

    select 41, 16480, 16532, NULL, 16631, NULL, NULL, NULL, 16880 UNION ALL

    select 42, NULL, NULL, NULL, 16999, 17089, NULL, 17245, NULL UNION ALL

    select 43, 17429, 17519, 17569, 17607, NULL, 17717, NULL, NULL UNION ALL

    select 44, 17845, NULL, NULL, NULL, NULL, NULL, NULL, NULL UNION ALL

    select 45, 18340, 18411, 18489, NULL, 18627, NULL, 18710, NULL UNION ALL

    select 46, 18761, 18858, 18891, 18911, NULL, 18923, 18938, NULL UNION ALL

    select 47, NULL, NULL, NULL, 19123, NULL, 19154, NULL, 19300 UNION ALL

    select 48, NULL, 19366, 19407, 19452, NULL, NULL, 19556, 19575 UNION ALL

    select 49, 19604, 19671, 19752, NULL, NULL, NULL, NULL, 19973 UNION ALL

    select 50, NULL, NULL, NULL, 20152, NULL, 20333, 20378, NULL UNION ALL

    select 51, 20453, NULL, 20516, NULL, 20650, NULL, NULL, 20760 UNION ALL

    select 52, NULL, NULL, NULL, 21017, NULL, NULL, NULL, NULL UNION ALL

    select 53, NULL, 21334, NULL, 21370, NULL, NULL, NULL, 21483 UNION ALL

    select 54, 21528, NULL, 21634, NULL, NULL, NULL, 21898, 21938 UNION ALL

    select 55, 21981, NULL, NULL, NULL, NULL, 22196, 22212, 22279 UNION ALL

    select 56, NULL, 22350, NULL, NULL, 22504, 22549, NULL, NULL UNION ALL

    select 57, NULL, 22710, 22795, 22869, NULL, NULL, NULL, 23018 UNION ALL

    select 58, 23054, NULL, 23199, 23262, NULL, NULL, NULL, 23439 UNION ALL

    select 59, 23536, 23629, NULL, 23787, 23820, NULL, 23909, 23974 UNION ALL

    select 60, NULL, 24124, 24189, 24197, NULL, 24243, NULL, 24419 UNION ALL

    select 61, 24431, 24470, 24510, NULL, NULL, NULL, NULL, 24704 UNION ALL

    select 62, 24793, NULL, 24857, NULL, 24941, 24975, 25022, 25114 UNION ALL

    select 63, NULL, 25301, 25397, 25457, NULL, NULL, 25505, 25506 UNION ALL

    select 64, 25539, NULL, 25635, NULL, NULL, 25775, 25789, NULL UNION ALL

    select 65, 25892, NULL, 26017, NULL, 26138, NULL, NULL, NULL UNION ALL

    select 66, 26395, 26485, 26533, 26583, 26610, NULL, NULL, NULL UNION ALL

    select 67, 26842, NULL, 26965, NULL, NULL, 27049, 27116, 27199 UNION ALL

    select 68, 27275, NULL, 27417, 27461, 27529, NULL, 27596, NULL UNION ALL

    select 69, 27756, 27818, 27842, NULL, 27947, 28007, NULL, NULL UNION ALL

    select 70, NULL, NULL, 28254, NULL, 28313, NULL, 28436, 28473 UNION ALL

    select 71, NULL, 28492, 28587, NULL, NULL, 28806, 28866, 28938 UNION ALL

    select 72, NULL, 29028, 29067, 29074, 29098, 29128, NULL, 29265 UNION ALL

    select 73, 29355, 29390, NULL, NULL, NULL, 29712, 29712, 29798 UNION ALL

    select 74, NULL, 29963, NULL, NULL, 30114, 30119, 30209, 30271 UNION ALL

    select 75, NULL, NULL, NULL, 30421, 30501, 30551, 30616, 30668 UNION ALL

    select 76, 30681, 30748, NULL, 30908, NULL, 30945, 31033, 31034 UNION ALL

    select 77, NULL, 31122, 31174, 31241, NULL, NULL, 31346, NULL UNION ALL

    select 78, 31409, NULL, 31527, NULL, 31578, NULL, NULL, NULL UNION ALL

    select 79, 31890, NULL, NULL, 31999, 32071, 32071, 32165, NULL UNION ALL

    select 80, 32328, NULL, NULL, 32409, 32476, NULL, NULL, NULL UNION ALL

    select 81, NULL, NULL, 32807, NULL, NULL, 32971, 33027, 33089 UNION ALL

    select 82, NULL, 33135, NULL, NULL, 33269, NULL, 33387, 33472 UNION ALL

    select 83, NULL, NULL, NULL, NULL, 33665, NULL, NULL, NULL UNION ALL

    select 84, 33862, NULL, 33976, NULL, 34121, NULL, NULL, 34294 UNION ALL

    select 85, NULL, 34359, NULL, 34484, 34512, NULL, NULL, NULL UNION ALL

    select 86, NULL, 34790, 34880, 34910, NULL, 35054, 35127, 35226 UNION ALL

    select 87, 35236, 35274, NULL, NULL, NULL, 35560, 35561, NULL UNION ALL

    select 88, 35622, NULL, 35727, NULL, 35859, NULL, NULL, 35921 UNION ALL

    select 89, 35955, NULL, 36043, NULL, NULL, 36209, NULL, NULL UNION ALL

    select 90, NULL, 36431, NULL, NULL, NULL, NULL, 36692, NULL UNION ALL

    select 91, NULL, NULL, 36860, NULL, 36951, 36969, 37035, NULL UNION ALL

    select 92, NULL, NULL, NULL, 37332, NULL, NULL, 37530, 37609 UNION ALL

    select 93, 37646, NULL, 37753, 37790, NULL, NULL, NULL, NULL UNION ALL

    select 94, 38033, 38044, NULL, NULL, NULL, 38258, NULL, NULL UNION ALL

    select 95, 38433, NULL, 38598, 38649, NULL, NULL, NULL, 38891 UNION ALL

    select 96, 38971, NULL, NULL, NULL, NULL, NULL, 39257, NULL UNION ALL

    select 97, 39288, NULL, NULL, NULL, 39451, 39499, 39526, 39569 UNION ALL

    select 98, NULL, 39610, NULL, NULL, 39706, 39739, 39814, 39895 UNION ALL

    select 99, 39982, 40050, 40144, 40242, NULL, NULL, NULL, NULL UNION ALL

    select 100, NULL, 40581, 40623, 40628, 40666, 40747, NULL, NULL

    )

    INSERT#Stage

    (

    ID,

    theCol,

    theValue

    )

    SELECTu.ID,

    u.theCol,

    u.theValue

    FROM(

    SELECT*

    FROMData

    ) AS s

    UNPIVOT(

    theValue

    FOR theCol IN (s.Col1, s.Col2, s.Col3, s.Col4, s.Col5, s.Col6, s.Col7, s.Col8)

    ) AS u

    WHILE @@ROWCOUNT > 0

    BEGIN

    SELECT@ID = ID

    FROM(

    SELECTs.ID,

    s.theCol,

    s.theValue,

    MIN(s.theValue) OVER (PARTITION BY s.theCol) AS minValue

    FROM#Stage AS s

    LEFT JOIN#Result AS r ON r.ID = s.ID

    WHEREr.ID IS NULL

    ) AS w

    GROUP BYID

    HAVINGSUM(CASE WHEN theValue <= minValue THEN 1 ELSE 0 END) = COUNT(*)

    IF @@ROWCOUNT > 0

    INSERT#Result

    (

    ID

    )

    SELECT@ID

    END

    DROP TABLE#Stage

    SELECTID,

    RowID

    FROM#Result

    ORDER BYRowID

    DROP TABLE#Result

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • I can spot the error in my algorithm now.

    Record 80 and 81 are mutually exclusive, because they have no common columns populated so both records fulfill the criteria of having all values as lowest values.

    So I need to add another WHERE clause in the case this scenario is true.


    N 56°04'39.16"
    E 12°55'05.25"

  • Peso (4/13/2009)


    I can spot the error in my algorithm now.

    Record 80 and 81 are mutually exclusive, because they have no common columns populated so both records fulfill the criteria of having all values as lowest values.

    So I need to add another WHERE clause in the case this scenario is true.

    Good news - will that take care of row 90 as well?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Yes, becuase records 88-89 are mutually exclusive with record 90, since neither one of record 88-89 have common columns with record 90.

    This edit seems to fix the problem

    GROUP BYID,

    minValue

    HAVINGSUM(CASE WHEN theValue <= minValue THEN 1 ELSE 0 END) = COUNT(*)

    ORDER BYminValue


    N 56°04'39.16"
    E 12°55'05.25"

  • Here is the revised algorithm, together with complete testcase from Mr Magoo.


    N 56°04'39.16"
    E 12°55'05.25"

  • Viewing 15 posts - 151 through 165 (of 180 total)

    You must be logged in to reply to this topic. Login to reply