-1

please help me write this query for MySQL. I want to select highest value from same datetime. So if there are multiple equal datetime value, return only one row with highest price. For summary, one row per timestamp with highest price.

+------+---------------+--------------+---------+---------+---------------------+
|  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 |
+------+---------------+--------------+---------+---------+---------------------+
2
  • Select. Timestamp, max(price) from T group by timestamp. Then you can join that with your table to get the rest of the columns Commented Aug 19, 2022 at 19:14
  • See the tag I added.
    – Rick James
    Commented Aug 20, 2022 at 1:07

2 Answers 2

1

For older MySQL versions one possible way to select the highest value from same datetime is using a subquery with the max price per datetime on the join condition.

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` ; 

The subquery select the max price grouped by the timestamp.

Because the price is not unique (same max price is repeated more than once for the same timestamp) I selected max id and then grouped by the other columns to get only one result per timestamp.

If you have MySQL 8+ , you could use row_number as the other answer mentioned.

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

Note. Please avoid using Keywords and Reserved Words such as timestamp in your case.

1
  • You probably need to group (or partition) by source, dest, timestamp inside the subquery and remove the group by and max completely from the main query. Commented Aug 21, 2022 at 11:38
1
With CTE AS (
Select *,(row_number() over(partition by timestamp order by price Desc)) as rn
From table
)

SELECT * FROM CTE 
WHERE rn = 1;

This Query will help you to get 1 row with max price for each timestamp.

4
  • Thank you mate. Could you rewrite your query to MySQL syntax?
    – Sahasrar
    Commented Aug 20, 2022 at 7:46
  • 1
    Above syntax is correct (fot MySQL 8.0+). (for docs, see: dev.mysql.com/doc/refman/8.0/en/… )
    – Luuk
    Commented Aug 20, 2022 at 11:40
  • 1
    You are missing FROM tablename inside the subquery Commented Aug 21, 2022 at 11:35
  • Thanks for figuring that out. Commented Aug 21, 2022 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.