Multi Column Sort

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 13, 2009 at 3:45 am

  #976437

  It will work with a more sparse data set than the one provided by Barry too. Here's how it works:

  Determine the minimum values for each column

  Identify the row which contains only minimum values or nulls - this is the "first" row

  Insert the ID of this row into a results table with an identity column

  Rinse and repeat:

  Determine the minimum values for each column excluding the rows which have already been captured into the results table.

  Which is roughly the same as this:

  DROP TABLE #Result

  CREATE TABLE #Result

  (RowID INT IDENTITY(1, 1),

  ID INT PRIMARY KEY CLUSTERED)

  --

  WHILE (SELECT COUNT(*) FROM #Result) < 100

  BEGIN

  INSERT INTO #Result ([ID])

  SELECT s.[ID]

  FROM SortedColumnsTestData s

  INNER JOIN (SELECT MIN(col1) AS col1, MIN(col2) AS col2, MIN(col3) AS col3, MIN(col4) AS col4, MIN(col5) AS col5, MIN(col6) AS col6, MIN(col7) AS col7, MIN(col8) AS col8

  FROM SortedColumnsTestData m WHERE NOT EXISTS (SELECT 1 FROM #Result WHERE [ID] = m.[ID])) m ON

  m.col1 = ISNULL(s.col1, m.col1)

  AND m.col2 = ISNULL(s.col2, m.col2)

  AND m.col3 = ISNULL(s.col3, m.col3)

  AND m.col4 = ISNULL(s.col4, m.col4)

  AND m.col5 = ISNULL(s.col5, m.col5)

  AND m.col6 = ISNULL(s.col6, m.col6)

  AND m.col7 = ISNULL(s.col7, m.col7)

  AND m.col8 = ISNULL(s.col8, m.col8)

  WHERE s.CorrectSequence <= 100

  END

  --

  SELECT s.*

  FROM SortedColumnsTestData s

  INNER JOIN #Result r ON r.[ID] = s.[ID]

  ORDER BY r.RowID

  A little more challenging is converting this RBAR code into something set-based.

  ^{“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

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  April 13, 2009 at 4:28 am

  #976451

  Greate job Chris!

  But you too will have to check for mutually exclusive records as with Mr Magoos test data.

  EDIT: I tried your algorithm with the sample data provided by Mr Magoo, and I can't get past record 98.

  ---

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

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 13, 2009 at 4:36 am

  #976453

  Peso, you seem to have got that spot on now - I have run through a few different sets of data from low to high population and it is quick and accurate. Great work!

  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]