SQL:使用Max选择最新数据的最新时间

Question

所以我有2个表。第一张表被命名为"名称"：

| uploadID  | Name | Freq |
---------------------------
| upload123 |  A   |  12  |
| upload345 |  A   |  12  |
| upload678 |  A   |  12  |

和第二个名为"上传"：

| uploadID  | uploadDate | uploadTime |
---------------------------------------
| upload123 | 2016-03-05 | 17:04:33 |
| upload345 | 2016-03-05 | 13:05:00 |
| upload678 | 2016-02-03 | 12:12:12 |

我想进行最新日期和时间的查询。预期的结果是：

| uploadID  | Name | Freq |
---------------------------
| upload123 |  A   |  12  |

因为Upload123具有最新的日期和时间。我尝试了：

SELECT * from name 
join upload
on name.upload id =upload=upload id 
where uploadDate = (SELECT MAX(upload.uploadDate) from upload) 
and uploadTime =(SELECT MAX(upload.uploadTime) from upload)

它没有任何结果。但是，当我不使用上传时间时，它可以工作，但以同一日期返回我2行。我认为我在使用汇总函数最大上传时犯了一个错误。但是我无法弄清楚。我正在使用PostgreSQL，并且上传时间的数据类型为Time

Answer 1

只需使用 ROW_NUMBER()：

SELECT * FROM (
    SELECT t.*,s.*,
           ROW_NUMBER() OVER(PARTITION BY t.upload_id ORDER BY s.uploadDate DESC,s.uploadTime DESC) as rnk
    FROM name t
    JOIN upload s
    ON(t.upload_id = s.upload_id)) p
WHERE p.rnk = 1

您查询的问题是最大time不必属于最大date。

| upload123 | 2016-03-05 | 17:04:33 |
| upload345 | 2016-02-05 | 18:05:00 |

这里2016-03-05是最大日期，18:05:00是最大时间。