如何編寫查詢以選擇同一日期的最大值

請幫我為 MySQL 編寫這個查詢。我想從同一日期時間中選擇最高值。因此，如果有多個相等的日期時間值，則只返回價格最高的一行。總而言之，每個時間戳一行，價格最高。

+------+---------------+--------------+---------+---------+---------------------+
|  id  | source_curr   | dest_curr    |  price  | act     |   timestamp         |
+------+---------------+--------------+---------+---------+---------------------+
|  164 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  769 | EUR           | USD          | 0.99125 | bids    | 2022-07-18 20:52:38 |
| 1370 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  568 | EUR           | USD          | 0.99322 | bids    | 2022-07-18 20:52:38 |
| 1572 | EUR           | USD          | 0.96335 | bids    | 2022-07-18 20:52:38 |
| 1771 | EUR           | USD          | 0.99432 | bids    | 2022-07-18 20:52:38 |
|  971 | EUR           | USD          | 0.99535 | bids    | 2022-07-18 20:52:38 |
|  367 | EUR           | USD          | 0.99334 | bids    | 2022-07-18 20:52:38 |
| 1173 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  574 | EUR           | USD          |  0.9865 | bids    | 2022-08-11 15:33:22 |
| 1179 | EUR           | USD          |  0.9845 | bids    | 2022-08-11 15:33:22 |
|  373 | EUR           | USD          |  0.9863 | bids    | 2022-08-11 15:33:22 |
| 1578 | EUR           | USD          |  0.9875 | bids    | 2022-08-11 15:33:22 |
|  977 | EUR           | USD          |  0.9868 | bids    | 2022-08-11 15:33:22 |
| 1376 | EUR           | USD          |  0.9864 | bids    | 2022-08-11 15:33:22 |
| 1777 | EUR           | USD          |  0.9365 | bids    | 2022-08-11 15:33:22 |
|  170 | EUR           | USD          |  0.9165 | bids    | 2022-08-11 15:33:22 |
|  775 | EUR           | USD          |  0.9865 | bids    | 2022-08-11 15:33:22 |
|  773 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1775 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1576 | EUR           | USD          |    0.98 | bids    | 2022-08-11 17:50:31 |
|  572 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
|  168 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
|  371 | EUR           | USD          |    0.95 | bids    | 2022-08-11 17:50:31 |
| 1177 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1374 | EUR           | USD          |    0.91 | bids    | 2022-08-11 17:50:31 |
|  975 | EUR           | USD          |    0.96 | bids    | 2022-08-11 17:50:31 |
|  581 | EUR           | USD          | 0.98465 | bids    | 2022-08-11 21:10:27 |
| 1784 | EUR           | USD          | 0.98564 | bids    | 2022-08-11 21:10:27 |
|  984 | EUR           | USD          | 0.98123 | bids    | 2022-08-11 21:10:27 |
|  782 | EUR           | USD          | 0.98132 | bids    | 2022-08-11 21:10:27 |
| 1383 | EUR           | USD          | 0.98115 | bids    | 2022-08-11 21:10:27 |
| 1585 | EUR           | USD          | 0.98848 | bids    | 2022-08-11 21:10:27 |
| 1186 | EUR           | USD          | 0.98185 | bids    | 2022-08-11 21:10:27 |
|  380 | EUR           | USD          | 0.98184 | bids    | 2022-08-11 21:10:27 |
|  177 | EUR           | USD          | 0.98148 | bids    | 2022-08-11 21:10:27 |
+------+---------------+--------------+---------+---------+---------------------+

對於較舊的 MySQL 版本，從同一日期時間選擇最高值的一種可能方法是在連接條件上使用每個日期時間的最大價格的子查詢。

select max(tt.id) as id,
      tt.source_curr,
      tt.dest_curr,
      tt.price,
      tt.act,
      tt.`timestamp`
from test_tbl tt
inner join (select `timestamp`,max(price) as price
           from test_tbl
           group by  `timestamp`
           ) as max_prc on tt.`timestamp`=max_prc.`timestamp` and tt.price=max_prc.price
group by source_curr,dest_curr,price,act,`timestamp` ; 

子查詢選擇按 . 分組的最高價格timestamp。

因為價格不是唯一的（同一時間戳重複多次相同的最高價格），所以我選擇了 max id，然後按其他列分組，每個時間戳只得到一個結果。

如果您有 MySQL 8+ ，則可以使用 row_number 作為提到的其他答案。

select id,source_curr,dest_curr,price,act,timestamp
from ( Select *,
            row_number() over(partition by timestamp order by price desc,id desc ) as rn
     from test_tbl 
    ) as tbl
where rn=1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ab5dd84bdb6f524c33e88b13c5c98450

注意。請避免在您的情況下使用關鍵字和保留字，例如時間戳。

引用自：https://dba.stackexchange.com/questions/315841