locked
SQL last record RRS feed

  • 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?









    Friday, January 26, 2018 1:43 PM

Answers

All replies

  • 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).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 26, 2018 2:07 PM
  • 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)

    Saturday, January 27, 2018 9:12 AM