priority based data rows SQL optimization

Table meas_point:

id
name

Table edge:

id
name

Table meas_point_data:

id
meas_point_id (foreign key)
date
value

Table edge_meas_point:

id
edge_id (foreign key)
meas_point_id (foreign key)
order (int)

Example Data:

meas_point(id, name):

1, meas1
2, meas2
3, meas3

edge(id, name):

1, edge1

edge_meas_point(id, edge_id, meas_point_id, order):

1, 1, 1, 3
2, 1, 2, 1
3, 1, 3, 2

meas_point_data(id, meas_point_id, date, value):

1, 1, 2018-12-20, 11
2, 2, 2018-12-20, 12
3, 3, 2018-12-20, 13
4, 1, 2018-12-21, 21
5, 3, 2018-12-21, 23
6, 1, 2018-12-22, 31
7, 1, 2018-12-23, 41
8, 2, 2018-12-23, 42
9, 3, 2018-12-23, 43

Idea

Table edge_meas_point combines edge with meas_point at given priority(order). If there is no data from meas point at order 1, order 2 should be taken and so on. For example:

SELECT TOP 1 d.date, d.value
    FROM meas_point_data AS d, edge_meas_point AS em
    WHERE
      em.meas_point_id=d.meas_point_id AND
      em.edge_id=1 AND
      d.date=2018-12-21
    ORDER BY em.order

working great for one date in WHERE, but what if I want to draw a chart from that data ? Based on this example my select should return:

2018-12-20 - 12
2018-12-21 - 23
2018-12-22 - 31
2018-12-23 - 42

In my real scenario date is datetime - every 15 minutes. And i have to get data for last month ..this gives as (60/15) * 24 * 30 = 2880 points, and I have to draw few edges on one chart. So how to do this efficiently ? (I have to use MS SQL)

Solution

If I understand correctly, you seem to want GROUP BY with some sort of "first" logic. However, there is not a "first" aggregation function, so you can use the window function:

SELECT DISTINCT d.date,
       FIRST_VALUE(d.value) OVER (PARTITION BY d.date ORDER BY em.order)
FROM meas_point_data d JOIN
     edge_meas_point em 
     ON em.meas_point_id = d.meas_point_id
WHERE em.edge_id = 1;