在T-SQL中获取最大记录 [英] Get Max record in T-SQL

问题描述

我有一种情况，我希望通过获取SQL Server中的最大记录来返回3列.像下面一样

I have a scenario where I would love to return 3 columns by getting the max record in SQL Server. like below

   ID   PID     PDATE                       Satus    CANUMBERS
   1    PS1     2015-08-05 12:20:49.627 0   1        CAA0322
   2    PS2     2015-08-06 04:05:52.053 0   1        CAA0322
   3    PS2     2015-08-07 07:17:32.653 1   1        CAA0322

在这种情况下，要查找的是每个PID的最大值PDate，就像一直输入的pid的最后一条记录一样.

in this case what am looking for is getting max PDate of each PID, like the last record of pid entered at all times.

我尝试了以下操作:

SELECT 
    MAX(ID), PID, PDATE   
FROM 
    TABLE1
GROUP BY 
    PID, PDATE  ...

将pdate分组的问题，它返回所有记录.我只想要

The problem with grouping the pdate, it returns all the records. i only want the max pdate for each PID like

1    PS1        2015-08-05 12:20:49.627 0   1        CAA0322
3    PS2        2015-08-07 07:17:32.653 1   1        CAA0322

请帮忙

推荐答案

您需要将CTE与ROW_NUMBER()窗口功能一起使用-类似于这样:

You need to use a CTE with a ROW_NUMBER() windowing function - something like this:

;WITH CTE AS
(
    SELECT 
        ID, PID, PDATE, Satus, CANUMERS,
        RowNum = ROW_NUMBER() OVER (PARTITION BY PID ORDER BY PDATE DESC)
    FROM
        dbo.YourTable
)
SELECT
    ID, PID, PDATE, Satus, CANUMERS
FROM
    CTE
WHERE
    RowNum = 1

PARTITION BY子句按列PID对数据进行分区，然后对每个分区从1开始进行编号-因此，具有RowNum = 1的行是最近行(按降序-最新的第一个)，每个PID

The PARTITION BY clause partitions your data by the column PID and then numbers each partition beginning from 1 - so the row with RowNum = 1 is the most recent row (ordered by PDATE descending - newest first) for each PID

这篇关于在T-SQL中获取最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！