Need help with SELECT and GROUP BY and SORT BY statement.

[Loogie]

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]

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