General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
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
Previous
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