Malcolm,
I'm not sure of SQL-Server syntax enough to offer a suggestion on that. However, in VFP the SELECT you show here wouuld result in the Song_No value being just one of the several possible values for each Room/Singer combination. In SQL-Server, this doesn't seem to work. If you need to show ALL the possible songs, then add Song_No to the GROUP clause. Otherwise you will have to leave it out. Since it is meaningless in the group context this is not unreasonable.

HTH
Barbara

>Hi All:
>
>I have a SQL SERVER 7.0 table with the following fields:
>ROOM_NO (int) ... SINGER_NO (int) ... SONG_NO (char)
>
>The entire table usually looks like this:
>ROOM_NO ... SINGER_NO ... SONG_NO
>3 ...................... 1 ........................ 24
>1 ...................... 2 ........................ 25
>1 ...................... 3 ........................ 30
>2 ...................... 3 ........................ 41
>1 ...................... 3 ........................ 23
>2 ...................... 2 ........................ 20
>2 ...................... 3 ........................ 99
>2 ...................... 3 ........................ 42
>1 ...................... 1 ........................ 36
>1 ...................... 5 ........................ 23
>3 ...................... 4 ........................ 20
>2 ...................... 6 ........................ 1
>
>I'd like to retrieve a UNIQUE RECORDSET (via ADO) using a WHERE on the Room_No in Singer_No ORDER. The following is the result I'm looking for.
>ROOM_NO ... SINGER_NO ... SONG_NO
>1 ...................... 1 ........................ 36
>1 ...................... 2 ........................ 25
>1 ...................... 3 ........................ 30
>1 ...................... 5 ........................ 30
>
>Although Singer_No 1 and 3 are in the table more than once for Room_No 1, I need the first occurance of the singer number only.
>
>At first it seemed simple, but I got nowhere :o(
>
>This does not work:
>SELECT * FROM MYTABLE ;
>WHERE Room_No = 1
>GROUP BY Singer_No
>ORDER BY Room_No, Signer_No
>
>The error message: column MAYTABLE.Song_No is invalid in the SELECT list becuase it is not contained in either an aggregate functionor the group by clause....
>
>What would be the proper syntax?
>
>Thanks for your help.
>
>Mal