CODEWITHSUNDEEP
sqlsql-server
Ties
Ties

Reputation: 71

Get always the highest value of each user in SQL Server

I'm doing some assignments for school but I'm getting stuck on this one. We need to get the most used language of each user. (The right column "Gebruikt" means used. Its a Dutch assignment so I need to do this in Dutch). Everything works but I only need one result of Irwin but what I try I cant get it to work.

Code vvvv

SELECT [Profile].ProfileName, Language.Name as 'Language', COUNT(*) as 'Gebruikt'
FROM (((Profile
INNER JOIN WatchedMedia ON [WatchedMedia].ProfileID = Profile.ProfileID)
INNER JOIN Subtitles ON [WatchedMedia].SubtitleID = Subtitles.SubtitleID)
INNER JOIN Language ON Language.LanguageID = Subtitles.LanguageID )
WHERE  Language.Name <> Profile.Language
GROUP BY Profile.ProfileName, WatchedMedia.ProfileID, Language.Name
ORDER BY  COUNT(*) DESC
GO

vvvv Result of code vvvv

enter image description here

Upvotes: 1

Views: 35

Answers (1)

forpas
forpas

Reputation: 164099

You can do it with ROW_NUMBER() window function:

SELECT t.ProfileName, t.Language, t.Gebruikt
FROM (
  SELECT 
    [Profile].ProfileName, Language.Name as Language, COUNT(*) as Gebruikt,
    ROW_NUMBER() OVER (PARTITION BY [Profile].ProfileName ORDER BY COUNT(*) DESC) rn    
  FROM Profile
  INNER JOIN WatchedMedia ON [WatchedMedia].ProfileID = Profile.ProfileID
  INNER JOIN Subtitles ON [WatchedMedia].SubtitleID = Subtitles.SubtitleID
  INNER JOIN Language ON Language.LanguageID = Subtitles.LanguageID 
  WHERE  Language.Name <> Profile.Language
  GROUP BY Profile.ProfileName, WatchedMedia.ProfileID, Language.Name
) t
WHERE t.rn = 1

Upvotes: 1

Related Questions