Get last data with two cases-CodePudding

iD      | UserID  | Type  | EntryDate  | Score
------------------------------------------------
1       | B000-1  | A     | 2022-04-25 | 90
2       | B000-1  | B     | 2022-04-26 | 70
3       | B000-1  | A     | 2022-04-28 | 70
4       | B000-2  | A     | 2022-04-24 | 90

WHERE UserID='B000-1' GROUP BY Type ORDER BY EntryDate DESC

OR get last data by Type and Date?

iD      | UserID  | Type  | EntryDate  | Score
------------------------------------------------
2       | B000-1  | B     | 2022-04-26 | 70
3       | B000-1  | A     | 2022-04-28 | 70

CodePudding user response：

Try something like this:

DECLARE @DataSource TABLE
(
    [ID] INT
   ,[UserID] VARCHAR(12)
   ,[Type] CHAR(1)
   ,[EntryDate] DATE
   ,[Score] INT
);

INSERT INTO @DataSource ([ID], [UserID], [Type], [EntryDate], [Score])
VALUES (1, 'B000-1', 'A', '2022-04-25', 90)
      ,(2, 'B000-1', 'B', '2022-04-26', 70)
      ,(3, 'B000-1', 'A', '2022-04-28', 70)
      ,(4, 'B000-2', 'A', '2022-04-24', 90);

SELECT TOP 1 WITH TIES iD, UserID, Type, EntryDate, Score
FROM @DataSource
ORDER BY ROW_NUMBER() OVER (PARTITION BY Type ORDER BY EntryDate DESC)