title: SQLDataReader sort order versus Stored procedure order
body: <p>I have a stored procedure that has <code>ORDER BY</code> clause for returning my records.
  When I execute in SQL I see appropriate order.</p>
  
  <p>However when I execute from the client and load into a <code>SqlDataReader</code>, the order has changed.  It's not the expected result. I get the same records, but the order (or maybe columns it's ordering on) has changed.</p>
  
  <p>What could be manipulating this? Or, what can I do in order to apply <code>ORDER BY</code> on the Reader at the client?</p>
  
  <p>The final SELECT out of the SP is:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT DISTINCT
       TotalMatchCount 
      ,ExampleDescription as Description 
      ,ExampleMatchCount 
      ,ExampleLevenshtein 
      ,Code6 as Code
      ,Tier1Code 
      ,Tier1Description 
      ,Tier1MatchCount 
      ,Tier1Levenshtein 
      ,Tier2Code 
      ,Tier2Description 
      ,Tier2MatchCount 
      ,Tier2Levenshtein 
      ,Tier3Code 
      ,Tier3Description 
      ,Tier3MatchCount 
      ,Tier3Levenshtein 
  FROM  
      @SearchResults 
  Order by 
       ExampleMatchCount Desc 
      ,ExampleLevenshtein ASC 
      ,Tier3MatchCount DESC 
      ,Tier3Levenshtein ASC 
      ,Tier2MatchCount DESC 
      ,Tier2Levenshtein ASC 
      ,Tier1MatchCount DESC 
      ,Tier1Levenshtein ASC 
  </code></pre>
  
  <p>Where a Result might be (only giving main description fields):</p>
  
  <p><code>getting hh child ready for school
   helping hh child with a school project
   waiting for school bus with hh child
   etc.</code></p>
  
  <p>On the client calling to SP:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Private SearchRDR As System.Data.SqlClient.SqlDataReader
  
  Dim db As DBAccess = New DBAccess
  Dim rdr As System.Data.SqlClient.SqlDataReader
  
      Try
          db.AddParameter("@pSearchText", _SearchText)
          rdr = db.ExecuteReader("sprc_SEL_Search")
  
          If rdr.HasRows Then
              'Get the data into the reader object
              SearchRDR = rdr
  </code></pre>
  
  <p>At this point I have checked the values in the <code>rdr</code> and they are already out of order. But in my code, when I go to load a data gridview I pull the <code>rdr</code> from its class and load the values:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Dim rdr As System.Data.SqlClient.SqlDataReader
  rdr = cSearch.SearchRDR
  
  Try
      If rdr.HasRows Then
  
          While rdr.Read()
              dgvSearch.Rows.Add(rdr("mycolumn").ToString() 
  . . . . . . . .
  </code></pre>
  
  <p>But as stated, it was already out of order before it got here. The results I see would similar to:</p>
  
  <p><code>waiting for school bus with nonhh child
   dropping off nonhh child at friend's house
   waiting for school bus with hh child</code></p>
  
  <p>Which is not the order shown above.  In fact the first item from SQL shows up in the reader at approximately position 4 (row 4).  Then second SQL items appears at Row 6.</p>
  
  <p>Ultimately there is NO alteration of order on the client.  It is just not using the order specified at the server.</p>