JackhammersForWeeks JackhammersForWeeks - 7 months ago 15
SQL Question

Retrieve all columns in row with min date?

Is there a way to solve something like this, without using a self join? Some way to use the min() function?

I want to get the first fruit entry for each group of columns c1 and c2. (Assume dates cannot be identical)

DROP TABLE IF EXISTS test;
CREATE TABLE test
(
c1 varchar(25),
c2 varchar(25),
fruit varchar(25),
currentTime Datetime
);
INSERT INTO test VALUES
('a','b','pineapple','2013-01-28 20:50:00'),
('a','b','papaya','2013-01-28 20:49:00'),
('a','b','pear','2013-01-28 20:51:00'),
('a','c','peach','2013-01-28 18:12:00'),
('a','c','plum','2013-01-28 20:40:00'),
('a','c','pluot','2013-01-28 16:50:00');


Here is my current query:

SELECT t2.*
FROM (SELECT c1,
c2,
MIN(currentTime) AS ct
FROM test
GROUP BY c1, c2) as t1
JOIN test t2
ON t1.c1 = t2.c1 AND
t1.c2 = t2.c2 AND
t2.currentTime = t1.ct


This yields the earliest entry for each
c1/c2
pair, but is there a way to use
min()
and avoid the self join?

Answer

The answer is "yes". You can do it with just aggregation. The key is to use the group_concat()/substring_index() trick to get the first fruit:

select c1, c2,
       substring_index(group_concat(fruit order by currentTime), ',', 1) as fruit,
       min(currentTime)
from test
group by c1, c2;

This has been tested on your SQL Fiddle.

Comments