按最大日期和ID分组

I have the following table:

我有下表：

 id         value       date
 ---------  ---------  ----------
 1          324          2017-05-19 02:00:00
 2          564          2017-05-19 04:00:00
 3          675          2017-05-19 03:00:00
 3          642          2017-05-19 05:00:00
 4          642          2017-05-19 07:00:00
 5          642          2017-05-19 06:00:00
 6          642          2017-05-19 05:00:00
 6          642          2017-05-19 12:00:00
 7          642          2017-05-19 01:00:00
 8          642          2017-05-19 13:00:00
 8          642          2017-05-19 02:00:00

I want to GROUP BY the id column but also select the value and date column from the first row when ordered by date descending, with that id.

我希望GROUP BY id列，但是当按日期降序排序时，也要从第一行中选择值和日期列。

The result should be like this:

结果应该是这样的：

 id         value       date
 ---------  ---------  ----------
 1          324          2017-05-19 02:00:00
 2          564          2017-05-19 04:00:00
 3          642          2017-05-19 05:00:00
 4          642          2017-05-19 07:00:00
 5          642          2017-05-19 06:00:00
 6          642          2017-05-19 12:00:00
 7          642          2017-05-19 01:00:00
 8          642          2017-05-19 13:00:00

5 个解决方案

#1

Something like this should work:

像这样的东西应该工作：

SELECT t1.id, t1.value, t1.date
FROM your_table t1
INNER JOIN (
    SELECT id, MAX(date) date
    FROM your_table
    GROUP BY id
) t2
ON t1.id = t2.id AND t1.date = t2.date

#2

Try this

尝试这个

select id, value, max(date) 
from table_name 
group by id,value; 

#3

Try this

尝试这个

select id, value, date 
from  name_table
group by id, value, date 

#4

Try this query...

试试这个查询......

SELECT t1.id,value,t1.date FROM new_table t1   
JOIN 
(SELECT nt.id,max(nt.date) AS date  FROM new_table as nt group by nt.id ) AS t2 
ON t1.date = t2.date AND t1.id = t2.id;

It will work..

它会工作..

Ask if any doubt.

询问是否有任何疑问。

#5

;With cte( id, value ,date)
AS
(

 SELECT 1, 324,'2017-05-19 02:00:00' Union all
 SELECT 2, 564,'2017-05-19 04:00:00' Union all
 SELECT 3, 675,'2017-05-19 03:00:00' Union all
 SELECT 3, 642,'2017-05-19 05:00:00' Union all
 SELECT 4, 642,'2017-05-19 07:00:00' Union all
 SELECT 5, 642,'2017-05-19 06:00:00' Union all
 SELECT 6, 642,'2017-05-19 05:00:00' Union all
 SELECT 6, 642,'2017-05-19 12:00:00' Union all
 SELECT 7, 642,'2017-05-19 01:00:00' Union all
 SELECT 8, 642,'2017-05-19 13:00:00' Union all
 SELECT 8, 642,'2017-05-19 02:00:00' 
 )
SELECT id
    ,value
    ,DATE
FROM (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY id ORDER BY id
            ) RNO
    FROM (
        SELECT id
            ,value
            ,MAX([date]) OVER (
                PARTITION BY id ORDER BY id DESC
                ) AS [date]
            ,ROW_NUMBER() OVER (
                PARTITION BY DATE ORDER BY id
                ) seq
        FROM cte
        ) ddt
    ) Final
WHERE Final.RNO = 1
ORDER BY Final.RNO

OutPut

产量

id  value   DATE
---------------------------
1   324     2017-05-19 02:00:00
2   564     2017-05-19 04:00:00
3   642     2017-05-19 05:00:00
4   642     2017-05-19 07:00:00
5   642     2017-05-19 06:00:00
6   642     2017-05-19 12:00:00
7   642     2017-05-19 01:00:00
8   642     2017-05-19 13:00:00