First off I am using VB.Net 2005 Compact Framework 2.0 with MS SQL 
Server 2005 compact.

I am stuck with an SQL Select statement that utilizes 4 columns. I wish 
to group 3 of them and sort on the forth using the FIRST function for 
the sorting column. However I understand that MS SQL Server 2005 Compact 
Edition does not support 'FIRST'

I have tried many ways to write the SQL and all throw an error. See 
below for what I have attempted.

Your help would be appreciated in getting me on track.

Thanks.

:L

"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 ]

"SELECT s_species, s_code, s_type, s_sort FROM prodspecies GROUP BY 
s_species, s_code, s_type ORDER BY s_sort"

Throws the following error:
In aggregate and grouping expressions, the SELECT clause can contain 
only aggregates and grouping expressions. [ Select clause = ,s_sort ]

Next I tried

"SELECT s_species, s_code, s_type FROM prodspecies GROUP BY s_species, 
s_code, s_type ORDER BY s_sort"

Throws the following error:
In aggregate and grouping expressions, the ORDER BY clause can contain 
only aggregate functions and grouping expressions.

Loogie wrote:

> First off I am using VB.Net 2005 Compact Framework 2.0 with MS SQL 
> Server 2005 compact.
> 
> I am stuck with an SQL Select statement that utilizes 4 columns. I wish 
> to group 3 of them and sort on the forth using the FIRST function for 
> the sorting column. However I understand that MS SQL Server 2005 Compact 
> Edition does not support 'FIRST'
> 
> I have tried many ways to write the SQL and all throw an error. See 
> below for what I have attempted.
> 
> Your help would be appreciated in getting me on track.
> 
> Thanks.
> 
> :L
> 
> "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 ]
> 
> "SELECT s_species, s_code, s_type, s_sort FROM prodspecies GROUP BY 
> s_species, s_code, s_type ORDER BY s_sort"
> 
> Throws the following error:
> In aggregate and grouping expressions, the SELECT clause can contain 
> only aggregates and grouping expressions. [ Select clause = ,s_sort ]
> 
> Next I tried
> 
> "SELECT s_species, s_code, s_type FROM prodspecies GROUP BY s_species, 
> s_code, s_type ORDER BY s_sort"
> 
> Throws the following error:
> In aggregate and grouping expressions, the ORDER BY clause can contain 
> only aggregate functions and grouping expressions.
> 
> 
> 


Just so ya know the solution to this was as follows:

"SELECT s_species, s_code, s_type, max(s_sort) da_sort FROM prodspecies 
GROUP BY s_species, s_code, s_type ORDER BY da_sort"


:L