1

In MariaDB / MySQL I have a table like this:

table (key1, key2, date) with INDEX(key1, key2)

Now I want to take the oldest entry:

SELECT * FROM `table` ORDER BY `date` ASC LIMIT 1

And finally all entries matching key1 and key2 from the first query:

SELECT * FROM `table` WHERE `key1` = ? AND `key2` = ?

Can this be simplified into one query somehow?

Rick James
  • 135,179
  • 13
  • 127
  • 222
user2015253
  • 1,263
  • 4
  • 14
  • 25

2 Answers2

3

Just use join:

select t.*
from `table` t join
     (select t.*
      from `table` t
      order by `date` asc
      limit 1
     ) tt
     on t.key1 = tt.key1 and t.key2 = tt.key2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

An alternative way to write this query is available since MariaDB 10.2 introduced Common Table Expressions. Using the WITH syntax the query looks like this:

WITH t_oldest AS (SELECT * FROM `table` ORDER BY `date` asc LIMIT 1)
SELECT t.* FROM `table` AS t,t_oldest 
WHERE t.key1 = t_oldest.key1 AND t.key2 = t_oldest.key2;
smile2day
  • 1,585
  • 1
  • 24
  • 34
  • That looks interesting. Do you know if this query will have better/worse performance than the JOIN solution by Gordon Linoff? – user2015253 Jul 18 '17 at 23:21
  • No, I didn't carry out any performance tests but I would be interested if any such results were available. I'd go along with Alex Pool's answer to a question on Stackoverflow which previously asked about differences between the two different approaches. [https://stackoverflow.com/questions/30078464/difference-between-with-clause-and-subquery] I personally find the WITH version more readable due to the use of a name (I used t_oldest in the above example). More complex queries will benefit even more in that aspect. – smile2day Jul 19 '17 at 14:20