如何使用SQL组筛选具有最大日期值的行

I have the following table

我有下表

CREATE TABLE Test
    (`Id` int, `value` varchar(20), `adate` varchar(20))
;

INSERT INTO Test
    (`Id`, `value`, `adate`)
VALUES
    (1, 100, '2014-01-01'),
    (1, 200, '2014-01-02'),
    (1, 300, '2014-01-03'),
    (2, 200, '2014-01-01'),
    (2, 400, '2014-01-02'),
    (2, 30 , '2014-01-04'),
    (3, 800, '2014-01-01'),
    (3, 300, '2014-01-02'),
    (3, 60 , '2014-01-04')
;

I want to achieve the result which selects only Id having max value of date. ie

我想实现只选择具有最大日期值的Id的结果。即

Id ,value ,adate

Id，价值，adate

 1, 300,'2014-01-03'     
 2, 30 ,'2014-01-04'     
 3, 60 ,'2014-01-04'

how can I achieve this using group by? I have done as follows but it is not working.

我如何使用group by实现这一目标？我做了如下但不起作用。

Select Id,value,adate
from Test
group by Id,value,adate
having adate = MAX(adate)

Can someone help with the query?

有人可以帮助查询吗？

4 个解决方案

#1

If you are using a DBMS that has analytical functions you can use ROW_NUMBER:

如果您使用的是具有分析功能的DBMS，则可以使用ROW_NUMBER：

SELECT  Id, Value, ADate
FROM    (   SELECT  ID,
                    Value,
                    ADate,
                    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Adate DESC) AS RowNum
            FROM    Test
        ) AS T
WHERE   RowNum = 1;

Otherwise you will need to use a join to the aggregated max date by Id to filter the results from Test to only those where the date matches the maximum date for that Id

否则，您需要使用连接到ID的聚合最大日期，以便将测试结果过滤到日期与该Id的最大日期匹配的结果

SELECT  Test.Id, Test.Value, Test.ADate
FROM    Test
        INNER JOIN
        (   SELECT  ID, MAX(ADate) AS ADate
            FROM    Test
            GROUP BY ID
        ) AS MaxT
            ON MaxT.ID = Test.ID
            AND MaxT.ADate = Test.ADate;

#2

Select the maximum dates for each id.

选择每个ID的最大日期。

select id, max(adate) max_date
from test
group by id

Join on that to get the rest of the columns.

加入其中以获得其余列。

select t1.*
from test t1
inner join (select id, max(adate) max_date
            from test
            group by id) t2
on t1.id = t2.id and t1.adate = t2.max_date;

#3

Please try:

请尝试：

select 
  * 
from 
  tbl a
where 
  a.adate=(select MAX(adate) from tbl b where b.Id=a.Id)

#4

-1

I would try something like this

我会尝试这样的事情

Select  t1.Id, t1.value, t1.adate
from    Test as t1
where   t1.adate = (select max(t2.adate) 
                    from Test as t2
                    where t2.id = t1.id)

#1