0

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:

enter image description here

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

  • This is MySQL? Always tag the rdbms? – Evan Carroll Apr 17 '18 at 21:14
  • What does it mean the first and last row, when you don't have an ORDER BY (the ordering is undefined) – Evan Carroll Apr 17 '18 at 21:14
  • @EvanCarroll Yes, that's MySQL. I get the value from the last 120 minutes and I need to change my query so that it will only get the latest and oldest record from those 120 minutes – michal Apr 17 '18 at 21:17
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); 
  • Thanks, In your query with UNION ALL i needed add brackets, before UNION ALL and after UNION ALL ;) – michal Apr 17 '18 at 21:41
  • Ah yes, I'll update the answer – Lennart Apr 17 '18 at 21:47
1

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.

0

If I've understood your requirement correctly, something like this could do the trick:

SELECT id, coins_id, MAX (my_time) -- &c...
WHERE my_time BETWEEN BETWEEN (NOW() - INTERVAL 120 minute) AND NOW()
FROM my_table
UNION
SELECT id, coins_id, MIN (my_time) -- &c...
WHERE my_time BETWEEN BETWEEN (NOW() - INTERVAL 120 minute) AND NOW()
FROM my_table;

Should work with all recent versions of MySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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