Hello Barbara:

Thank you for you reply.

Actually, the result I'm looking for should resemble a DBF table with a FILTER on Room_No (i.e. Room_No = 1) and one each of the Singer_No in Singer_No order. I can do it in a DBF table using SEEK and an ARRAY, but, it doesn't help in using a SQL SERVER table.

The resulting RecordSet should have as many rows as there are Singer_No but no duplicates Singer_no (i.e. If there were 15 Singer_No 2's, I would only ned to see ONE Singer_No = 2). The WHERE Clause would take care of Room_No. The Song_No is whatever that was inputted into the Song_No Field of that row.

Is this not possible? No one seems to have an answer.

Again, thank you,

Mal

>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