April 9, 2009 at 11:59 pm
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"
April 10, 2009 at 5:31 am
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]
April 10, 2009 at 5:46 am
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"
April 10, 2009 at 6:24 am
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!
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
April 10, 2009 at 8:16 am
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 @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
April 10, 2009 at 8:56 am
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]
April 10, 2009 at 1:44 pm
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"
April 10, 2009 at 4:18 pm
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);
April 10, 2009 at 10:18 pm
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.
April 13, 2009 at 2:48 am
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"
April 13, 2009 at 3:06 am
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
)
SELECT u.ID,
u.theCol,
u.theValue
FROM (
SELECT *
FROM Data
) 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 (
SELECT s.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
WHERE r.ID IS NULL
) AS w
GROUP BY ID
HAVING SUM(CASE WHEN theValue <= minValue THEN 1 ELSE 0 END) = COUNT(*)
IF @@ROWCOUNT > 0
INSERT #Result
(
ID
)
SELECT @ID
END
DROP TABLE #Stage
SELECT ID,
RowID
FROM #Result
ORDER BY RowID
DROP TABLE #Result
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
April 13, 2009 at 3:14 am
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"
April 13, 2009 at 3:15 am
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);
April 13, 2009 at 3:17 am
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 BY ID,
minValue
HAVING SUM(CASE WHEN theValue <= minValue THEN 1 ELSE 0 END) = COUNT(*)
ORDER BY minValue
N 56°04'39.16"
E 12°55'05.25"
April 13, 2009 at 3:40 am
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