次の方法で共有

What Was the One Millionth Post in the Forums?

  • [アーティクル]

James asked a good question in the comments section of my last blog post, and I thought the topic might be a fun blog post to end a Friday on.  What exactly was the one millionth post?

Well, the one millionth post on the MSDN Forums was the fifth post in a thread entitled "Results to row instead of column" in the Transact-SQL forum.  The post was made by the user Sami Samir Ibrahim who joined the site on March 12th and has contributed nine posts so far.

Here it is...the text of the 1,000,000th MSDN Forums post...yet another answer from a user that enjoys helping other people:

Well, here is a solution although not a really very user firendly one. The idea is first to create a query that contains the row ID. If you are using SQL Server 2005 then you can use the Row_Number() function. If not, then the only way I could think of was to create a temp table with an identity field. After that you need to join the same result set together but with incrementing IDs. Meaning join ID 1 in the first result set with ID 2 in the second one and then join this result set with ID 3 in the 3rd one. Told you it was not a very user friendly answer :)
For SQL Server 2005 the query will be:
Select Top 1 A.[FirstName] + ', ' + B.[FirstName] + ', ' + C.[FirstName]
From (Select Row_Number() Over (order by [FirstName]) as RowID, [FirstName] From [AdventureWorks].[Person].[Contact]) A
Inner Join (Select Row_Number() Over (order by [FirstName]) as RowID, [FirstName] From [AdventureWorks].[Person].[Contact]) B on (A.RowID + 1) = B.RowID
Inner Join (Select Row_Number() Over (order by [FirstName]) as RowID, [FirstName] From [AdventureWorks].[Person].[Contact]) C on (B.RowID + 1) = C.RowID
For SQL Server 2000 it will be:
Create Table #Tmp
(RowID int identity(1,1),
FirstName varchar(256))
Insert Into #Tmp (FirstName)
Select [FirstName] From [AdventureWorks].[Person].[Contact]
Order By [FirstName]
Select Top 1 A.[FirstName] + ', ' + B.[FirstName] + ', ' + C.[FirstName]
From #Tmp A Inner Join #Tmp B on (A.RowID + 1) = B.RowID
Inner Join #Tmp C on (B.RowID + 1) = C.RowID
Drop Table #Tmp
I hope this helps.
Best regards,
Sami Samir

Comments

  • Anonymous
    April 05, 2007
    We've recently released a service pack for the forums that have changed some functionality in the forums.