Select first and last rows between two dates MySQL

Question

I have query:

SELECT id, coins_id, first_coin, second_coin, price, `time`
FROM hist_all
WHERE `time` between (NOW() - interval 120 minute) AND NOW()
  AND (second_coin = 'USD' OR second_coin = 'USDT')
  AND first_coin = 'LSK';

Query return:

How I can get first and last rows relative to the date ? (In this example first record is id=1832, last id=14117)

Answer 1

In all due time there will be Common Table Expressions (CTE) in MySQL:

WITH T AS (
   SELECT id, coins_id, first_coin, second_coin, price, `time`
   FROM hist_all
   WHERE `time` between (NOW() - interval 120 minute) AND NOW()
     AND (second_coin = 'USD' OR second_coin = 'USDT')
     AND first_coin = 'LSK'
) 
(SELECT * FROM T ORDER BY time LIMIT 1)
UNION ALL
(SELECT * FROM T ORDER BY time DESC LIMIT 1);

If your version of MySQL dont support CTE's you can repeat the query:

  (SELECT id, coins_id, first_coin, second_coin, price, `time`
   FROM hist_all
   WHERE `time` between (NOW() - interval 120 minute) AND NOW()
     AND (second_coin = 'USD' OR second_coin = 'USDT')
     AND first_coin = 'LSK'
   ORDER BY TIME LIMIT 1)
   UNION ALL
  (SELECT id, coins_id, first_coin, second_coin, price, `time`
   FROM hist_all
   WHERE `time` between (NOW() - interval 120 minute) AND NOW()
     AND (second_coin = 'USD' OR second_coin = 'USDT')
     AND first_coin = 'LSK'
   ORDER BY time DESC LIMIT 1); 

Answer 2

Using CTEs with MySQL 8

WITH t1 AS (
  SELECT id, coins_id, first_coin, second_coin, price, `time`
  FROM hist_all
  WHERE `time` between (NOW() - interval 120 minute) AND NOW()
    AND (second_coin = 'USD' OR second_coin = 'USDT')
    AND first_coin = 'LSK'
) AS t
WITH max_min AS t2 (
  SELECT max(time) AS max,min(time) AS min FROM t1
)
SELECT max_min.*, tmax.*, tmin.*
FROM max_min
INNER JOIN t1 AS tmax ON t1.time = max_min.max
INNER JOIN t1 AS tmin ON t1.time = max_min.min;

Using Window Functions with MySQL 8

SELECT *
FROM (
  SELECT time = min(time) OVER () OR time = max(time) OVER () AS sel,
    id, coins_id, first_coin, second_coin, price, `time`
  FROM hist_all
  WHERE `time` between (NOW() - interval 120 minute) AND NOW()
    AND (second_coin = 'USD' OR second_coin = 'USDT')
    AND first_coin = 'LSK'
) AS t
WHERE t.sel;

No MySQL 8?

Get PostgreSQL Today, it's freer, not owned by Oracle and better.