Malcolm,

PMFJI, but it seems as if the song_no is not relevant since it appears that you want a grouping of singer_no(s) within a particular room_no, that leaves you with a couple of choices...

1 - Leave song_no out of the SQL: Select room_no, singer_no from sometable group by room_no, singer_no

2 - Use an aggregate function on song_no: Select room_no, singer_no, count(song_no) from sometable group by room_no, song_no

Mark

>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