Hello Cetin:

Sorry, I take it back. I didn't work after all. The first time I tried it it didn't return an error message. By the way, I am using ADO (thru OLE DB) to SQL Server 7.0. Was this info needed? If so, sorry.

It seems the problem is in the GROUP BY clause.
If I do a
"SELECT * FROM MyTable ;
WHERE Room = 1 ;
GROUP BY Room, Singer_No ;
ORDER BY Singer_No"

The ADO OLE PROVIDER message is:
Column MYTABLE.Song_No is invalid in the select list because it is not contained in either an aggregate funtion or the GROUP BY clause ...

Still need help, please.
Thanks in advance.

Mal


>>Hi:
>>
>>I have a table:
>>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
>>
>>Using a WHERE on the Room_No and getting ONE unique set of consecutive Singer_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(
>>
>>Thanks for your help.
>>
>>Mal
>
>

---

select * from mytable ;
>  group by singer_no ;
>  order by room_no, singer_no

Cetin