I have a table Test...

Time   Location
10      A  
1       B  
2       C  
3       D  
4       E

And I want to return all records between max value of Time and a given negative offset of max Time, For example, Time >= 10 and Time <= (10 - 7)... So Time range would be 3 to 10, thus query result would be...

Time   Location
10      A  
4       E  
3       D

The table is constantly gaining new records, where Time is always increasing for each record, so this is sliding range for column Time.

I tried something like...

SELECT *, MAX(Time) 
From Test 
where (Time <= MAX(Time) And (Time >= (MAX(Time) - 7))

But at least in MariaDB SQL syntax you cannot use MAX in WHERE clause? I get an 'Invalid use of group function' error, so clearly this query is more complex than I expected?

I have a table Test...

Time   Location
10      A  
1       B  
2       C  
3       D  
4       E

And I want to return all records between max value of Time and a given negative offset of max Time, For example, Time >= 10 and Time <= (10 - 7)... So Time range would be 3 to 10, thus query result would be...

Time   Location
10      A  
4       E  
3       D

The table is constantly gaining new records, where Time is always increasing for each record, so this is sliding range for column Time.

I tried something like...

SELECT *, MAX(Time) 
From Test 
where (Time <= MAX(Time) And (Time >= (MAX(Time) - 7))

But at least in MariaDB SQL syntax you cannot use MAX in WHERE clause? I get an 'Invalid use of group function' error, so clearly this query is more complex than I expected?

• sql
• mysql
• mariadb

Share Improve this question Follow edited Mar 9 at 5:29 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 8 at 3:57 Dachshund DigitalDachshund Digital 1111 silver badge11 bronze badge 1

• FYI you can use x BETWEEN a AND b instead of x >= a AND x <= b – Barmar Commented Mar 8 at 5:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

You can use a subquery or CTE to fetch the highest time of your table.

Sidenote: TIME is a function (see the documentation here), so better rename this column if possible.

It's likely enough to check that the time is greater than max(time) - offset. The other condition is not necessary because a time greater than the max time of your table can't exist. In this case, this simple query will do:

SELECT
  time, 
  location
FROM test
WHERE time >= (SELECT MAX(time) - 7 AS time FROM test)
ORDER BY time DESC; -- remove the sort if not required

Maybe you need the full logic for other purposes, therefore I leave this in the rest of my answer. In this case, you can JOIN on the subquery or CTE using BETWEEN.

See this db<>fiddle with your sample data.

Using a pure subquery with full logic:

SELECT
  t.time, 
  t.location
FROM test t
INNER JOIN (SELECT MAX(time) AS time FROM test) m
  ON t.time BETWEEN m.time - 7 AND m.time
ORDER BY t.time DESC; -- remove the sort if not required

Using a CTE:

WITH max_time AS
(SELECT MAX(time) AS time FROM test)
SELECT
  t.time, 
  t.location
FROM test t
INNER JOIN max_time m
  ON t.time BETWEEN m.time - 7 AND m.time
ORDER BY t.time DESC; -- remove the sort if not required