• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

Need help with SQL GROUP BY (MS SQL SERVER 2005 compact edition)

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.

Thanks

PBLack
0
PBLack
Asked:
PBLack
  • 6
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the only thing I see that is "wrong" is the function FIRST, which does not exist in SQL Server
0
 
PBLackAuthor Commented:
Ok so here is what I have tried
"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.

Now I have no idea how to fix it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
"SELECT s_species, s_code, s_type, max(s_sort) FROM prodspecies GROUP BY s_species, s_code, s_type ORDER BY max(s_sort)"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PBLackAuthor Commented:
angelIII I got an error as follows:

Expressions in the ORDER BY list cannot contain aggregate functions.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Expressions in the ORDER BY list cannot contain aggregate functions.
looks like that is a limitation of the compact edition

can you try this:

select * from (
SELECT s_species, s_code, s_type, max(s_sort) s_sort FROM prodspecies GROUP BY s_species, s_code, s_type
) l
ORDER BY s_sort
0
 
PBLackAuthor Commented:
angelIII:

An error was thrown again
There was an error parsing the query. [ Token line number = 1,Token line offset = 16,Token in error = SELECT ]

I tried without the | too with the same result.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have gone through the docs, and this should work:

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

0
 
PBLackAuthor Commented:
angelIII

We have a winner. Works the very best. On this hot Thursday (97 degrees right now) this is a welcomed relief. Think I will now go drink a beer.

I owe you a beer too!

Later and thanks so much.

PBLack
0
 
PBLackAuthor Commented:
PS...

You said you went through the 'Docs' Where can I get my own copy?

Thanks

PBLack
0
 
PBLackAuthor Commented:
thanks again
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now