I have the following table:

The table has 8 lines. I want a table having only non-repeating intStudentCode. For Example:

I dont have preference if the strFavoriteSocialMedia for intStudentCode = 1 is Facebook or Instagram. The same to intStudentCode = 6. All I want is retrieve a single student line based on intStudentCode. PS: I am using SQL Server 2012, but if you have the solution for another DBMS, it also help.

CodePudding user response：

Since you don't have a preference if the strFavoriteSocialMedia a simple aggregation should do the trick

Select intStudentCode
      ,strFavoriteSocialMedia = max( strFavoriteSocialMedia )
 From  YourTable
 Group By intStudentCode

Or another option using WITH TIES in concert with row_number()

Select top 1 with ties *
 From  YourTable
 Order By row_number() over (partition by intStudentCode order by strFavoriteSocialMedia)

CodePudding user response：

To add-up, Another way that would help using Row_Number

SELECT intStudentCode,strFavoriteSocialMedia  FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY intStudentCode ORDER BY strFavoriteSocialMedia) as r,* 
FROM Table
)TEMP WHERE r=1