coldfusioncoldfusion-9cfmlqoq

Distinct QoQ automatically applying order by


I am using CFSpreadsheet to read a .xlsx file.

The file has about 3000 duplicates which I can safely ignore so I thought I'd do a select distinct QoQ but once I do this, the results are ordered as if order by col_1, col_2 was added to the query which is a very bad thing.

<cfspreadsheet query = "qSheet" ...>
<cfquery dbtype="query" name = "qDistinctSheet">
    select distinct
          col_1
        , col_2
    from
        qSheet
</cfquery> 
<cfdump var = "#qDistinctSheet#">

If I remove distinct I get the expected results which SHOULD be:

  1. [empty string]
  2. Name
  3. John
  4. John
  5. Adam
  6. Steve
  7. Bob
  8. Bob

When I add distinct I get

  1. [Empty String]
  2. Adam
  3. Bob
  4. John
  5. Name
  6. Steve

Any idea how to prevent this unwanted ordering?

Edit

End solution is to apply a row number and use group by as suggested by Matt and Dan

<cfset ids = []>
<cfloop query="qSheet">
    <cfset ids[qSheet.currentRow] = qSheet.currentRow>
</cfloop>
<cfset queryAddColumn(qSheet,"id",ids)>
<cfquery dbtype="query" name="qDistinct">
    SELECT  
          col_1
        , col_2
        , min(ID) AS firstID
    FROM
        qSheet
    GROUP BY    
        col_1
        , col_2
    ORDER BY
        firstID
</cfquery>

Solution

  • You can use a GROUP BY option instead and use the ID row from the spreadsheet query

    <cfquery dbtype="query" name="qDistinct">
    SELECT  
          col_1
        , col_2
        , min(ID) AS firstID
    FROM
        qSheet
    GROUP BY    
        col_1
        , col_2
    ORDER BY
        firstID