can't select unique records based on multiple criteria

Monday, May 12, 2014

I have an Excel sheet report formatted like this:



+----------+--------+-------------+
| file | column | columnIndex |
+----------+--------+-------------+
| abc.xlsx | test | 14 |
| def.xlsx | test | 55 |
| abc.xlsx | xyz | 19 |
| def.xlsx | xyz | 19 |
+----------+--------+-------------+


I'm trying to query it with ADODB so that I can get the unique pairs of column and columnIndex values where column is not blank.


I have tried various queries but each one has given me an error.



select [report$].column,[report$].columnIndex from [report$] group by [report$].column,[report$].columnIndex having [report$].column<>'' order by columnIndex


gives this error:



No value given for one or more required parameters.


This query:



select [report$].column,[report$].columnIndex from [report$] group by [report$].column,[report$].columnIndex where [report$].column<>'' order by columnIndex


gives this error:



Syntax error (missing operator) in query expression '[report$].columnIndex where [report$].column<>'''.


This query:



select distinct [report$].column,[report$].columnIndex from [report$] where [report$].column<>'' order by columnIndex


gives this error:



ORDER BY clause (columnIndex) conflicts with DISTINCT.


How can I write this query to get the unique values?







http://ift.tt/1nz2X8f