學習資訊部落

一、介紹

以下為來源資料

二、語法

使用　ROW_NUMBER()會自訂編號，且必須有 OVER 子句。
（）中間請輸入要產生的規則。

１．只有排序規則

SELECT *,ROW_NUMBER() Over (Order By [code] ASC) As unmber0
From [dbo].[Table_SQL_1]

２．欄位加排序規則

SELECT *,ROW_NUMBER() Over (Partition By [code],[type] Order By [code],[type] ASC) As unmber
From [dbo].[Table_SQL_1]

產生結果

三、取得最新一筆的方法如下：

SELECT *,ROW_NUMBER() Over (Partition By [code],[type] Order By [unmber0] DESC) As unmber
FROM (
SELECT  [id],[code],[type],[text1],[text2],[text3],ROW_NUMBER() Over (Order By [dbo].[Table_SQL_1].[code],[dbo].[Table_SQL_1].[type] DESC) As unmber0
From [dbo].[Table_SQL_1]
) as k

再用「WHERE unmber=1」就是最新一筆資料了。

語法如下：

SELECT *
    FROM (
        SELECT *,ROW_NUMBER() Over (Partition By [code],[type] Order By [unmber0] DESC) As unmber
        FROM (
        SELECT  [id],[code],[type],[text1],[text2],[text3],ROW_NUMBER() Over (Order By [dbo].[Table_SQL_1].[code],[dbo].[Table_SQL_1].[type] DESC) As unmber0
        From [dbo].[Table_SQL_1]
        ) as k
    ) as k1
WHERE [unmber]=1