General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
To Whom That May Be Interested:
SERGEY'S SOLUTION THIS WORKS!!!
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
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only