SQL last record

Question

User932259438 posted

Hi,

I have a large database and I want to retrieve last record.

Example:
Id, FirstName, LastName
1, firstname1, lastname1
2, firstname1, lastname1
3, firstname1, lastname1
4, firstname2, lastname2
5, firstname3, lastname3
6, firstname3, lastname3
7, firstname4, lastname4

I need to get:
Id, FirstName, LastName
3, firstname1, lastname1
4, firstname2, lastname2
6, firstname3, lastname3
7, firstname4, lastname4

How can I do with mssql?

Answer 1

User753101303 posted

Hi,

Try SELECT MAX(Id),FirstName,LastName FROM YourTable GROUP BY FirstName,LastName. See https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql for details.

Note though that strictly speaking it is not impossible to have two distinct persons with the same FirstName, LastName (ie if you try to clean up an existing database you could have this issue).

Answer 2

User932259438 posted

Hi,

I have one question.

How can I use this sql code for retrieve:

		SELECT MAX(TS.SuggestionId),COUNT(TS.SuggestionId) OVER (PARTITION BY TS.MatchId) AS SuggestionCount, TS.SuggestionId,TS.SpecialBetValue,TS.MatchId,TS.OutCome,OT.Code,SC.SCount AS CountSuggestion,cast(round(ISNULL(TS.Value,0),2) as numeric(36,2)) AS VALUE
				,OT.NoOfColumnDisplay, ISNULL(TS.RemainingLimit,0) AS RemainingLimit,TM.CategoryId
		FROM [dbo].[tbl_Suggestions] TS
		INNER JOIN dbo.tbl_Matches TM ON TS.MatchId=TM.MatchId AND TM.SportId = 1 AND  TM.MatchDate >=GETDATE()  AND TS.IsLatest=1 AND  TM.Isopen=1 AND TS.IsOpen=1
		INNER JOIN @SuggestionCount SC ON SC.MatchId=TM.MatchId
		INNER JOIN dbo.tbl_OddsTypes OT ON TS.OddsType=OT.OddsTypeId AND OT.OddsTypeId=10
		WHERE TS.OutCome!='-1'  AND TM.StatusInfoOff=0
		GROUP BY TS.SuggestionId,TS.Value,TS.OutCome,OT.Code,TM.CountSuggestion,TS.SpecialBetValue,MatchDate,OT.NoOfColumnDisplay,TS.MatchId,SC.SCount,TS.RemainingLimit,TM.CategoryId,OutcomeId
		order by TM.MatchDate,SpecialBetValue,OutcomeId asc

I need use this code with suggestionId last rows.

Group by: Outcome, OutcomeId, MatchId using MAX(TS.SuggestionId)