0

Using MySQL 5.7. I have a table called 'data_points' that contains time series data. There are the following columns:

  • data_point_id
  • asset_name
  • data_point_time
  • data_point_value
  • tag_name

It looks like this, but goes on for several thousand rows:

data_point_id  asset_name  data_point_time   data_point_value  tag_name 
1               asset1      6/12/2018 1:30   40                tag1
2               asset2      6/11/2018 22:18  60                tag2
3               asset3      6/11/2018 19:06  75                tag3
4               asset2      6/11/2018 15:54  10                tag1
5               asset5      6/11/2018 12:42  300               tag2

I want to be able to select the last 10 chronological records for each combination of asset_name and tag_name. I've determined that the following query gives me the output I want for a singular asset_name and tag_name combination, but I'm looking for a way to get all of the asset_name and tag_name combinations.

select * from data_points where tag_name = 'tag1' and asset_name = 'asset1' order by data_point_time desc limit 10;

From there I would need to perform another select, as union doesn't seem to work to join them all together. I suspect this has something to do with the limit 10.

My attempt to get all the distinct combinations of asset_name and tag_name looked like this:

select * from data_points where tag_name in (select distinct concat(asset_name, tag_name) from data_points) order by data_point_time desc limit 10;

This doesn't work for a couple of reasons, but the most obvious to me is that the limit 10 applies to the entire select so only 10 records are returned.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
nickelcap
  • 71
  • 1
  • 11
  • Let's pretend you want the last 3 for each combination, and there are, say, 15 rows. Good. Now see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jun 12 '18 at 13:13
  • What you are looking for is `GROUP BY` – JohnnyJS Jun 12 '18 at 13:13
  • I have a feeling that you have to use UNION to join multiple queries, each with an ORDER BY, with a WHERE condition on one tag name. You may need to use some programmatic means (Java or stored procedures) to construct the query. Good luck! – leeyuiwah Jun 12 '18 at 13:58

0 Answers0