Problem with Distinct and Order By when querying csv with .NET System::Data::OleDb::OleDbConnection [modified]
-
I need to query a csv file with C++ .NET and get a distinct list which is ordered by multiple criterion. If I write regular SQL, then i get the error "order by clause conflicts with distinct". If I use the prefix "SELECT * FROM ( SELECT" and suffix ")", then the order is lost. My current workaround is to manually modify the results to a distinct set using C++.
modified on Wednesday, February 10, 2010 5:24 AM
-
I need to query a csv file with C++ .NET and get a distinct list which is ordered by multiple criterion. If I write regular SQL, then i get the error "order by clause conflicts with distinct". If I use the prefix "SELECT * FROM ( SELECT" and suffix ")", then the order is lost. My current workaround is to manually modify the results to a distinct set using C++.
modified on Wednesday, February 10, 2010 5:24 AM
-
What is the exact query you are using?
"No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem
The query with the distinct is below. However, this returns an error unless distinct is removed. I am using Provider=Microsoft.Jet.OLEDB.4.0 SELECT DISTINCT ID_BB_SEC_NUM_DES FROM Curncy_FORWARD2010020801.csv WHERE ID_BB_SEC_NUM_DES LIKE '%HKD%' AND NAME NOT LIKE '%/%' AND NAME NOT LIKE '%+%' AND UCASE(NAME) NOT LIKE '%FWD POINTS%' AND UCASE(NAME) NOT LIKE '%IMPLIED YLD%' AND UCASE(NAME) NOT LIKE '%OUTR%' AND LEN(ID_BB_SEC_NUM_DES) BETWEEN 5 AND 10 AND UCASE(MARKET_SECTOR_DES( = 'CURNCY' AND SECURITY_TYP='FORWARD' AND ID_BB_SEC_NUM_SRC IS NOT NULL AND RIGHT(ID_BB_SEC_NUM_DES,1) IN ('W','M','Y') ORDER BY RIGHT(ID_BB_SEC_NUM_DES,1)<>'W' DESC, RIGHT(ID_BB_SEC_NUM_DES,1), LEFT(RIGHT(ID_BB_SEC_NUM_DES,LEN(ID_BB_SEC_NUM_DES)-3),LEN(ID_BB_SEC_NUM_DES)-4)+0