I am using vb.net 2005 compact framework with MS SQL Server 2005 compact.
The following select statement using GROUP BY will not work even though it works in MS Access 97. What is going on and how can I fix it?
"SELECT s_species, s_code, s_type, FIRST(s_sort) AS DAsort FROM prodspecies GROUP BY s_species, s_code, s_type ORDER BY FIRST(s_sort)
I also tried
"SELECT s_species, s_code, s_type, FIRST(s_sort) AS DAsort FROM prodspecies GROUP BY s_species, s_code, s_type ORDER BY DAsort
I am getting an error as follows:
In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = ,s_sort ]
I need the first three columns. There is some duplicate data in the originating table which I am trying to eliminate from my results using 'group by'. I just need to sort on the s_sort column but I don't want it grouped.