Hellooooo Sergey!!

Your solution worked!! I got exactly what was needed!!

To be honest, I don't understand that Syntax you gave me :o(.
But, it's fast and IT WORKS!!

Thank you very much.

Mal


>Malcolm,
>
>By definition, physical order of rows in reletational database has to be/is irrelevant.
>
>You’ll have to add one extra column to your mytable:
> ID (int) IDENTITY(1,1)
>to express ‘physical order’ of rows in the way that SQL SERVER can understand and use.
>
>A following SQL statement will return required result:
>
>SELECT * FROM MyTable
>WHERE Id IN (
>SELECT MIN(Id) AS Id FROM MYTABLE
>WHERE Room_No = 1
>GROUP BY Room_no, Singer_No)
>ORDER BY Room_No, Singer_No
>
>>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