如何在SQL中获取最新的更新记录？

debugcn 发表于 Dev

用户名

我已经写过查询，但是没有得到最新的更新数据。我的意思是我有psn update列，我想要最新更新日期的当前positiondata，根据“ latestpsnupdate”，我需要当前位置。

SELECT 
     k.KeyWord,
     pa.UserName,
     pa.ProjId,
     k.KeywordID,
     kp.currentposition,
     kp.PsnUpdateDate
FROM Tbl_ProjAssignment pa 
INNER JOIN Tbl_keywords k on k.KeywordID=pa.KeywordID 
INNER JOIN seo.Tbl_KeywordPosition kp 
ON kp.keywordid = pa.keywordid   
WHERE k.ProjId='105' ORDER BY PsnUpdateDate DESC

o / p

keyword        UserName ProjId   keywordid currentposition  PsnUpdateDa
ventis123       Tester    105     1453          6            2015-03-11 
ventis123       krishna   105     1453          6            2015-03-11
annuity         Tester    105     1449          8            2015-01-30 
annuity         Tester    105     1449          7            2015-01-27 
ventissitnev    Tester    105     1452          6            2015-01-16 
annuity         Tester    105     1449          55           2015-01-15 
annuity         Tester    105     1449          6            2015-01-08 
annuity         Tester    105     1449          10           2014-12-13

需要o / p

所需的输出/输出

keyword      UserName ProjId   keywordid currentposition  PsnUpdateDa
ventis123     Tester    105     1453        6              2015-03-11 
annuity       Tester    105     1449        8              2015-01-30
ventissitnev  Tester    105     1452        6              2015-01-16 
annuity       Tester    105     1449        55             2015-01-15

莫斯塔法·阿曼迪（Mostafa Armandi）

用法如下：

;WITH    CTE
AS ( SELECT k.KeyWord ,
            pa.UserName ,
            pa.ProjId ,
            k.KeywordID ,
            kp.currentposition ,
            kp.PsnUpdateDate ,
            ROW_NUMBER() OVER ( PARTITION BY k.keyword ORDER BY kp.PsnUpdateDate DESC ) AS positiondata
    FROM     Tbl_ProjAssignment pa
            INNER JOIN Tbl_keywords k ON k.KeywordID = pa.KeywordID
            INNER JOIN seo.Tbl_KeywordPosition kp ON kp.keywordid = pa.keywordid
    )

SELECT  *
FROM    CTE
WHERE   ProjId = '105'
        AND positiondata = 1

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。