0

I have the following table:

intStudentCode strFavoriteSocialMedia
1 Facebook
1 Instagram
2 Twitter
3 TikTok
4 Youtube
5 Facebook
6 TikTok
6 Youtube

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

intStudentCode strFavoriteSocialMedia
1 Facebook
2 Twitter
3 TikTok
4 Youtube
5 Facebook
6 TikTok

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.

Stu
  • 30,392
  • 6
  • 14
  • 33
Guilherme Massoqueto
  • 37
  • 4
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Feb 02 '22 at 18:41

2 Answers2

4

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)
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

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
waqar shahbaz
  • 34
  • 5