如何通过SQL Server中的列按max（日期）获取数据

In the table below I need lastservice data based on the last date group by nopol.

在下表中,我需要基于nopol的最后一个日期组的lastservice数据。

Table data:

I tried to use the query below

我试着使用下面的查询

SELECT 
    TGL, A.NOPOL, LASTSERVICE 
FROM 
    TRREALPRWT AS A
INNER JOIN 
    (SELECT 
         MAX(TGLREAL) AS TGL, NOPOL 
     FROM 
         TRREALPRWT 
     GROUP BY 
         NOPOL) AS B ON B.NOPOL = A.NOPOL
GROUP BY 
    A.NOPOL, TGL,LASTSERVICE

but the results obtained are not per nopol.

但获得的结果不是每个nopol。

What I should do from the query so that it produces data like the following

我应该从查询中做什么,以便它生成如下的数据

| NOPOL   | LASTSERVICE | TGLREAL                   |
| L9235VB | 224270      | 2018-01-26 00: 00: 00.000 |
| B9891JT | 219270      | 2018-02-28 00: 00: 00.000 |

4 个解决方案

#1

Take all MAX(TGL) for all NOPOL in an inner query and join it again with your original table matching on NOPOL and MAX(TGL) values.

在内部查询中获取所有NOPOL的所有MAX(TGL)并再次将其与原始表匹配在NOPOL和MAX(TGL)值上。

SELECT T.NOPOL, T.TGL, T.LASTSERVICE
FROM
YOUR_TABLE T
INNER JOIN
(SELECT NOPOL, MAX(TGL) AS MAX_TGL FROM YOUR_TABLE GROUP BY NOPOL) A
ON T.NOPOL = A.NOPOL AND T.TGL = A.MAX_TGL;

#2

I believe this should work, assuming TGLREAL is your date.

我认为这应该有效,假设TGLREAL是你的约会对象。

SELECT TGL,A.NOPOL,LASTSERVICE FROM TRREALPRWT
WHERE TGLREAL IN (SELECT MAX(TGLREAL) FROM  TRREALPRWT)

#3

The sample output in the question seems to indicate that you want the first tgl for each nopol, max(lastservice)

问题中的示例输出似乎表明您想要每个nopol的第一个tgl,max(lastservice)

SELECT nopol,MIN(lastservice) lastservice,  MAX(tgl) tglreal
FROM trreal
GROUP BY nopol

#4

Is seems to be use only group by clause no need to inner query

似乎只使用group by子句无需内部查询

select nopol, min(LASTSERVICE) LASTSERVICE, max(TGL) TGLREAL  
from TRREALPRWT 
group by nopol

#1