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)

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;