Mysql Join 2 table and select maximum and minimum value between date range

Question

i have 2 table, orders and rates. i want to join this two tables and select maximum and minimum value between opentime and closetime

Table 1 : orders

id  type    pair    lot     opentime        openprice   closeprice  closetime 

1   buy     eurusd  0.01    2016-05-02 02:04:07     1.15112     1.14778     2016-05-02 03:05:00

2   sell    gbpusd  0.01    2016-05-02 02:24:17     1.45221     1.44989     2016-05-02 03:05:00

Table 2 : rates

id  pair    time            price

10  eurusd  2016-05-02 03:00:00     1.14522

9   gbpusd  2016-05-02 03:00:00     1.44726

8   eurusd  2016-05-02 02:30:00     1.15258

7   gbpusd  2016-05-02 02:30:00     1.45311

6   eurusd  2016-05-02 02:00:00     1.15051

5   gbpusd  2016-05-02 02:00:00     1.45173

4   eurusd  2016-05-01 01:30:00     1.14258

3   gbpusd  2016-05-02 01:30:00     1.44326

2   eurusd  2016-05-02 01:00:00     1.15751

1   gbpusd  2016-05-02 01:00:00     1.45911

expected results

id type pair    lot     opentime     openprice closeprice closetime       high    timehigh      low     timelow

1  buy  eurusd  0.01 2016-05-02 02:04:07 1.15112   1.14778    2016-05-02 03:05:00 1.15258 2016-05-02 02:30:00   1.14522 2016-05-02 03:00:00

2  sell gbpusd  0.01 2016-05-02 02:24:17 1.45221   1.44989    2016-05-02 03:05:00 1.45311 2016-05-02 02:30:00   1.44726 2016-05-02 03:00:00

i try this query but get empty result

SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime,high,timehigh,low,timelow FROM (SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime FROM `order` ORDER BY closetime DESC) table1 
JOIN (SELECT MAX(price) as high,time as timehigh,pair as pairhigh FROM `rates` GROUP BY pair) table2 ON table1.pair=table2.pairhigh 
JOIN (SELECT MIN(price) as low,time as timelow,pair as pairlow FROM `rates` GROUP BY pair) table3 ON table1.pair=table3.pairlow 
WHERE table2.timehigh between table1.opentime and table1.closetime AND table3.timelow between table1.opentime and table1.closetime

try query without where clause get result not empty but not expected

SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime,high,timehigh,low,timelow FROM (SELECT id,type,pair,lot,opentime,openprice,closeprice,closetime FROM `order` ORDER BY closetime DESC) table1 
JOIN (SELECT MAX(price) as high,time as timehigh,pair as pairhigh FROM `rates` GROUP BY pair) table2 ON table1.pair=table2.pairhigh 
JOIN (SELECT MIN(price) as low,time as timelow,pair as pairlow FROM `rates` GROUP BY pair) table3 ON table1.pair=table3.pairlow

Result

id type pair    lot     opentime     openprice closeprice closetime       high    timehigh      low     timelow

1  buy  eurusd  0.01 2016-05-02 02:14:07 1.15112   1.14778    2016-05-02 03:05:00 1.15751 2016-05-02 02:00:00   1.14258 2016-05-02 02:00:00

2  sell gbpusd  0.01 2016-05-02 03:24:17 1.45221   1.44989    2016-05-02 03:05:00 1.45911 2016-05-02 02:00:00   1.44326 2016-05-02 02:00:00

how to solve this?

Answer 1

I believe this now matches your requirements?

SELECT *
FROM Orders
JOIN (SELECT price as maxPrice, pair, tr_time as maxTime FROM Rates
    JOIN (SELECT Rates.pair, MAX(Rates.price) AS price
        FROM Rates, Orders 
        WHERE (Rates.tr_time between Orders.opentime and Orders.closetime) 
        GROUP BY Rates.pair) 
    as MaxPrices USING (price, pair)) maxRates USING (pair)
JOIN (SELECT price AS minPrice, pair, tr_time as minTime FROM Rates
        JOIN (SELECT Rates.pair, MIN(Rates.price) AS price
            FROM Rates, Orders 
            WHERE (Rates.tr_time between Orders.opentime and Orders.closetime) 
            GROUP BY Rates.pair) 
    as minPrices USING (price, pair)) minRates USING (pair);

Your code structure is too poor for me to really work out what is going on, but essentially it seems that you were unaware that aggregate operators such as MAX() return only a single value.

You've also been unclear on what you wanted the minimum/maximum price of (I've assumed its per pair)

Have a run of the code and see if it returns right? If not tell me where it doesn't match and I can start patching it!

EDIT New results:

Answer 2

Try this, it outputs as your expected result, may work for you;)

SQL Fiddle

MySQL 5.6 Schema:

CREATE TABLE orders
    (`id` int, `type` varchar(4), `pair` varchar(6), `lot` float, `opentime` datetime, `openprice` float, `closeprice` float, `closetime` datetime)
;

INSERT INTO orders
    (`id`, `type`, `pair`, `lot`, `opentime`, `openprice`, `closeprice`, `closetime`)
VALUES
    (1, 'buy', 'eurusd', 0.01, '2016-05-02 02:04:07', 1.15112, 1.14778, '2016-05-02 03:05:00'),
    (2, 'sell', 'gbpusd', 0.01, '2016-05-02 02:24:17', 1.45221, 1.44989, '2016-05-02 03:05:00')
;


CREATE TABLE rates
    (`id` int, `pair` varchar(6), `time` datetime, `price` float)
;

INSERT INTO rates
    (`id`, `pair`, `time`, `price`)
VALUES
    (10, 'eurusd', '2016-05-02 03:00:00', 1.14522),
    (9, 'gbpusd', '2016-05-02 03:00:00', 1.44726),
    (8, 'eurusd', '2016-05-02 02:30:00', 1.15258),
    (7, 'gbpusd', '2016-05-02 02:30:00', 1.45311),
    (6, 'eurusd', '2016-05-02 02:00:00', 1.15051),
    (5, 'gbpusd', '2016-05-02 02:00:00', 1.45173),
    (4, 'eurusd', '2016-05-01 01:30:00', 1.14258),
    (3, 'gbpusd', '2016-05-02 01:30:00', 1.44326),
    (2, 'eurusd', '2016-05-02 01:00:00', 1.15751),
    (1, 'gbpusd', '2016-05-02 01:00:00', 1.45911)
;

Query 1:

SELECT DISTINCT
orders.*, mx.high, R1.time AS timehigh, mn.low, R2.time AS timelow
FROM orders
LEFT JOIN (
    SELECT orders.pair, rates.time, max(rates.price) AS high
    FROM orders
    JOIN rates ON orders.pair = rates.pair AND rates.time <= orders.closetime AND rates.time >= orders.opentime
    group by orders.pair
) mx ON mx.pair = orders.pair
LEFT JOIN (
    SELECT orders.pair, min(rates.price) AS low, rates.time
    FROM orders
    JOIN rates ON orders.pair = rates.pair AND rates.time <= orders.closetime AND rates.time >= orders.opentime
    GROUP BY orders.pair
) mn ON mn.pair = orders.pair
LEFT JOIN rates R1 ON mx.pair = R1.pair and R1.price = mx.high
LEFT JOIN rates R2 ON mn.pair = R2.pair and R2.price = mn.low

Results:

| id | type |   pair |  lot |              opentime | openprice | closeprice |             closetime |    high |                  time |     low |                  time |
|----|------|--------|------|-----------------------|-----------|------------|-----------------------|---------|-----------------------|---------|-----------------------|
|  1 |  buy | eurusd | 0.01 | May, 02 2016 02:04:07 |   1.15112 |    1.14778 | May, 02 2016 03:05:00 | 1.15258 | May, 02 2016 02:30:00 | 1.14522 | May, 02 2016 03:00:00 |
|  2 | sell | gbpusd | 0.01 | May, 02 2016 02:24:17 |   1.45221 |    1.44989 | May, 02 2016 03:05:00 | 1.45311 | May, 02 2016 02:30:00 | 1.44726 | May, 02 2016 03:00:00 |

Answer 3

I think davidhood2's nailed it, but just in case...

SELECT a.* 
     , b.price high
     , b.time timehigh
     , c.price low
     , c.time timelow
  FROM orders a
  JOIN
     ( SELECT x.pair
            , x.price 
            , x.time
         FROM rates x 
         JOIN  
            ( SELECT r.pair
                   , MAX(r.price) price 
                FROM rates r 
                JOIN orders o 
                  ON o.pair = r.pair 
                 AND r.time BETWEEN o.opentime AND o.closetime 
               GROUP 
                  BY pair
            ) y 
           ON y.pair = x.pair 
          AND y.price = x.price
     ) b
    ON b.pair = a.pair
  JOIN
     ( SELECT x.pair
            , x.price 
            , x.time
         FROM rates x 
         JOIN  
            ( SELECT r.pair
                   , MIN(r.price) price 
                FROM rates r 
                JOIN orders o 
                  ON o.pair = r.pair 
                 AND r.time BETWEEN o.opentime AND o.closetime 
               GROUP 
                  BY pair
            ) y 
           ON y.pair = x.pair 
          AND y.price = x.price
     ) c
    ON c.pair = a.pair;

+------+------+--------+------+---------------------+-----------+------------+---------------------+---------+---------------------+---------+---------------------+
| id   | type | pair   | lot  | opentime            | openprice | closeprice | closetime           | high    | timehigh            | low     | timelow             |
+------+------+--------+------+---------------------+-----------+------------+---------------------+---------+---------------------+---------+---------------------+
|    1 | buy  | eurusd | 0.01 | 2016-05-02 02:04:07 |   1.15112 |    1.14778 | 2016-05-02 03:05:00 | 1.15258 | 2016-05-02 02:30:00 | 1.14522 | 2016-05-02 03:00:00 |
|    2 | sell | gbpusd | 0.01 | 2016-05-02 02:24:17 |   1.45221 |    1.44989 | 2016-05-02 03:05:00 | 1.45311 | 2016-05-02 02:30:00 | 1.44726 | 2016-05-02 03:00:00 |
+------+------+--------+------+---------------------+-----------+------------+---------------------+---------+---------------------+---------+---------------------+