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:
When I add distinct I get
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>
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