0

Can somebody please explain the reason for the phenomenon I am seeing? I am going to paste the screenshot of the code and output below to make it easy to explain my problem.

Problem: The Left Join that I am performing between two tables is sorting my output data alphabetically when I just select particular columns but not when I select all columns in my left table.

Code 1: This is where the sorting problem is coming:

SQLQuery = _
_
"SELECT TOP 10 VersionNumber, CLA_VersionNumber " & _
_
"FROM (" & _
        "[FullPolicyList$] AS FPL) " & _
        "LEFT JOIN (SELECT CLA_VersionNumber FROM [ClaimsList$]) AS CL ON CL.[CLA_VersionNumber] 
= FPL.[VersionNumber] "

Output 1: Open this link to see how the output looks like, the result is sorted A-Z

Code 2:

 SQLQuery = _
_
  "SELECT TOP 10 * " & _
_
 "FROM (" & _
        "[FullPolicyList$] AS FPL) " & _
        "LEFT JOIN (SELECT CLA_VersionNumber FROM [ClaimsList$]) AS CL ON CL.[CLA_VersionNumber] 
= FPL.[VersionNumber] "

Output 2: Open this link to see how the output now looks like, the result is sorted in how it is in the left table, without alphabetically sorting

What do you think is causing the sorting issue when selecting particular columns instead of all columns?

Kunjesh
  • 1
  • 3
  • 3
    You haven't provided an `ORDER BY`, so the order in which rows are returned is unpredictable, and can change between executions. The "sorting issue" is the absence of sorting. – HoneyBadger Sep 21 '21 at 07:53
  • 1
    You do not have an `order by` in either query, you therefore cannot make any assumption on the row ordering, the differences are from the internal operations carried out by the query optimizer. – Stu Sep 21 '21 at 07:53
  • If you want a predicable sorting order, add `ORDER BY` to your query. – The Impaler Sep 21 '21 at 13:44

0 Answers0