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