在这种情况下，我需要如何更改我的sql才能得到我想要的

Question

我有一个如下表：

id  value    date
1    5    2015-01-10
2    5    2015-06-13
3    5    2015-09-05
4    11   2015-02-11
5    11   2015-01-10
6    11   2015-01-25

可以看出，每个value出现3次，date不同。我想写一个查询，返回具有最大date的唯一values，上表如下：

id  value    date
3    5    2015-09-05
4    11   2015-02-11

我该怎么做？

这是最新的问题：

我遇到的真正问题比上面的简化版本稍微复杂一点。我以为一旦我知道了简化版本的答案，我可以更进一步，但我很抱歉我错了。所以，我在这里更新这个问题。

我有两张表格，如下所示：

      Table 1
id  id2    date
1    2   2015-01-10
2    5   2015-06-13
3    9   2015-09-05
4    10  2015-02-11
5    26  2015-01-10
6    65  2015-01-25
      Table 2
id  id2    data
1    2       A
2    5       A
3    9       A
4    10      B
5    26      B
6    65      B

这里，Table 1和Table 2由id2 连接

我想要得到的是以下两条记录：

id2  date       data
9   2015-01-10    A
10  2015-02-11    B

Answer 1

您可以使用row_number来选择每个值具有最大日期的行

select * from (
    select t2.id2, t1.date, t2.data, 
        row_number() over (partition by t2.data order by t1.date desc) rn
    from table1 t1
    join table2 t2 on t1.id = t2.id2
) t where rn = 1

Answer 2

select a.id, a.value, a.date
from mytable a,
   ( select id, max(date) maxdate
       from mytable b
     group by id) b
where a.id = b.id
  and a.date = b.maxdate;

Answer 3

Oracle设置:

CREATE TABLE Table1 ( id, id2, "date" ) AS
SELECT 1,    2,   DATE '2015-01-10' FROM DUAL UNION ALL
SELECT 2,    5,   DATE '2015-06-13' FROM DUAL UNION ALL
SELECT 3,    9,   DATE '2015-09-05' FROM DUAL UNION ALL
SELECT 4,    10,  DATE '2015-02-11' FROM DUAL UNION ALL
SELECT 5,    26,  DATE '2015-01-10' FROM DUAL UNION ALL
SELECT 6,    65,  DATE '2015-01-25' FROM DUAL;
CREATE TABLE Table2 ( id, id2, data ) AS
SELECT 1,    2,       'A' FROM DUAL UNION ALL
SELECT 2,    5,       'A' FROM DUAL UNION ALL
SELECT 3,    9,       'A' FROM DUAL UNION ALL
SELECT 4,    10,      'B' FROM DUAL UNION ALL
SELECT 5,    26,      'B' FROM DUAL UNION ALL
SELECT 6,    65,      'B' FROM DUAL;

查询：

SELECT MAX( t1.id  ) KEEP ( DENSE_RANK LAST ORDER BY t1."date" ) AS id,
       MAX( t1.id2 ) KEEP ( DENSE_RANK LAST ORDER BY t1."date" ) AS id2,
       MAX( t1."date" ) AS "date",
       t2.data
FROM   Table1 t1
       INNER JOIN
       Table2 t2
       ON ( t1.id = t2.id AND t1.id2 = t2.id2 )
GROUP BY t2.data

输出：

        ID        ID2 date                DATA
---------- ---------- ------------------- ----
         3          9 2015-09-05 00:00:00 A    
         4         10 2015-02-11 00:00:00 B    

查询2：

SELECT id,
       id2,
       "date",
       data
FROM   (
  SELECT t1.*,
         t2.data,
         ROW_NUMBER() OVER ( PARTITION BY t2.data ORDER BY t1."date" DESC ) AS rn
  FROM   Table1 t1
         INNER JOIN
         Table2 t2
         ON ( t1.id = t2.id AND t1.id2 = t2.id2 )
)
WHERE  rn = 1;

输出：

        ID        ID2 date                DATA
---------- ---------- ------------------- ----
         3          9 2015-09-05 00:00:00 A    
         4         10 2015-02-11 00:00:00 B