SELECT MIN from a subset of data obtained through GROUP BY

Question

There is a database in place with hourly timeseries data, where every row in the DB represents one hour. Example:

TIMESERIES TABLE

id     date_and_time        entry_category
1      2017/01/20 12:00     type_1
2      2017/01/20 13:00     type_1
3      2017/01/20 12:00     type_2
4      2017/01/20 12:00     type_3

First I used the GROUP BY statement to find the latest date and time for each type of entry category:

SELECT MAX(date_and_time), entry_category
FROM timeseries_table
GROUP BY entry_category;

However now, I want to find which is the date and time which is the LEAST RECENT among the datetime's I obtained with the query listed above. I will need to use somehow SELECT MIN(date_and_time), but how do I let SQL know I want to treat the output of my previous query as a "new table" to apply a new SELECT query on? The output of my total query should be a single value—in case of the sample displayed above, date_and_time = 2017/01/20 12:00.

I've tried using aliases, but don't seem to be able to do the trick, they only rename existing columns or tables (or I'm misusing them..).There are many questions out there that try to list the MAX or MIN for a particular group (e.g. https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ or Select max value of each group) which is what I have already achieved, but I want to do work now on this list of obtained datetime's. My database structure is very simple, but I lack the knowledge to string these queries together.

Thanks, cheers!

What's the expected result in case of a tie? (I.e. two different rows with that date_and_time value.) — jarlh, Mar 25 '19 at 10:43
@jarlh in case there is a tie, I just want **one** of those rows to be displayed. It's not important which `entry_category` has the "oldest" `date_and_time`, and I just want a single value as output :) — StiltonElk, Mar 25 '19 at 10:59

Gordon Linoff · Answer 1 · 2019-03-25T11:12:47.207

0

Is this what you want?

SELECT TOP 1 MAX(date_and_time), entry_category
FROM timeseries_table
GROUP BY entry_category
ORDER BY MAX(date_and_time) ASC;

This returns ties. If you do not want ties, then include an additional sort key:

SELECT TOP 1 MAX(date_and_time), entry_category
FROM timeseries_table
GROUP BY entry_category
ORDER BY MAX(date_and_time) ASC, entry_category;

edited Mar 25 '19 at 11:12

answered Mar 25 '19 at 10:46

Gordon Linoff

1,242,037
58
646
786

Yes, this would be what I'm looking for, very elegant! Is the `TOP 1 WITH TIES` also MS access syntax? I seem to be getting an error when trying to run... – StiltonElk Mar 25 '19 at 11:02
@StiltonElk . . . Ooops. That is SQL Server syntax. In MS Access, `TOP 1` automatically includes ties and they need to be removed using additional keys in the `ORDER BY`. – Gordon Linoff Mar 25 '19 at 11:13

score 0 · Accepted Answer · answered Mar 25 '19 at 10:46

0

You can use your first query as a sub-query, it is similar to what you are describing as using the first query's output as the input for the second query. Here you will get the one row out put of the min date as required.

SELECT MIN(date_and_time)
FROM (SELECT MAX(date_and_time) as date_and_time, entry_category
FROM timeseries_table
GROUP BY entry_category)a;

answered Mar 25 '19 at 10:46

Ajan Balakumaran

1,639
1
8
16

1

This also works as advertised, thank you! I did have to change one variable name (the alias name and reference to it) in order to avoid a "circle reference". It now looks like this `SELECT MIN(datetime_2) FROM (SELECT MAX(date_and_time) as datetime_2, entry_category FROM timeseries_table GROUP BY entry_category)a;` – StiltonElk Mar 25 '19 at 11:11

SELECT MIN from a subset of data obtained through GROUP BY

2 Answers2