1

I am trying to use a MySQL query to select data from three tables, as following:

pv
t_id
t_name

pv_values
v_id
t_id
value
datetime

alm
alm_id
t_id
datetime
status
priority

I want to get the last 10 values from pv_values for the last 5 rows of alm that have priority "1", and also get the name from pv for those values.

This is my query, but it only gives me the 10 last values of the last alm value:

SELECT pv_values.*, pv.tag_name
FROM `pv_values`,`pv`
WHERE pv_values.`tag_id` = (
    SELECT `tag_id`
    FROM `alarms` as `alt`
    WHERE `alt`.`tag_id` = `pv`.`tag_id` and `alt`.`priority` = 1
    ORDER BY `alarm_id` DESC
    LIMIT 1
)

Limit 10

I expect to get:

pv_values.v_id pv_values.t_id pv_values.value pv_values.datetime pv.t_name 
1              1
2              1
3              1
4              1
5              1
6              1
7              1
8              1
9              1
10             1
1              2
2              2
3              2
4              2
5              2
6              2
7              2
8              2
9              2
10             2

where t_id came from the alm.t_id last 5.

  • could you please build an example, because limit 10 means you only would get 10 rows not 20 as you expected – nbk Nov 8 at 18:57
  • I want to get 10 rows from pv_values for each 1 in alm – Abdula Nov 8 at 19:03
  • so the total for rows will be 5*10 = 50 rows that every 5 rows from pv_values will match 1 tag_id from alm – Abdula Nov 8 at 19:04
1

I like to work with with Data that you select, so that you can understand it better, waht i am doing, when you recognized algorithsm.

So with this data

CREATE TABLE alarmes
    (`ID` int, `tag_id` int,  `alarm` varchar(8))
;

INSERT INTO alarmes
    (`ID`,`tag_id`,  `alarm`)
VALUES
    (1,1,  'Alarm1'),
    (2,2, 'Alarm2'),
    (3,3,  'Alarm3'),
    (4,4,  'Alarm4')
;
CREATE TABLE pv_values
    (`ID` int, tag_id int ,  `values` varchar(8))
;

INSERT INTO pv_values
    (`ID`, `tag_id`, `values`)
VALUES
    (1,1 , 'value1'),
    (2, 1, 'value2'),
    (3, 1, 'value3'),
    (4, 1, 'value4'),
    (5,1 , 'value5'),
    (6, 1, 'value6'),
    (7, 1, 'value7'),
    (8, 1, 'value8'),
    (9,1 , 'value9'),
    (10, 1, 'value10'),
    (11, 1, 'value11'),
    (12, 1, 'value12'),
    (1,4 , 'value1'),

    (2, 5, 'value2'),
    (3, 4, 'value3'),
    (4, 4, 'value4'),
    (5,4 , 'value5'),
    (6, 4, 'value6'),
    (7, 4, 'value7'),
    (8, 4, 'value8'),
    (9,4 , 'value9'),
    (10, 4, 'value10'),
    (11, 4, 'value11'),
    (12, 4, 'value12'),
        (1,2 , 'value1'),
    (2, 2, 'value2'),
    (3, 2, 'value3'),
    (4, 2, 'value4'),
    (5,2, 'value5'),
    (6, 2, 'value6'),
    (7, 2, 'value7'),
    (8, 2, 'value8'),
    (9,2, 'value9'),
    (10, 2, 'value10'),
    (11, 2, 'value11'),
    (12, 2, 'value12'),
    (1,3 , 'value1'),
    (2, 3, 'value2'),
    (3, 3, 'value3'),
    (4, 3, 'value4'),
    (5,3 , 'value5'),
    (6, 3, 'value6'),
    (7, 3, 'value7'),
    (8, 3, 'value8'),
    (9,3 , 'value9'),
    (10, 3, 'value10'),
    (11, 3, 'value11'),
    (12, 3, 'value12')
;

You can use in mysql 5.x this statement

SELECT rnk, t1.`tag_id`, `values`,alarm
FROM
(Select 
  *
  ,IF(@tag_id<>tag_id, @rank := 0, @rank := @rank +1) rnk
  ,@tag_id := tag_id
From pv_values,(SELECT @rank := 1) r,(SELECT @tag_id := 0) re
Where tag_id in (SELECT tag_id FROM alarmes)) t1 inner join alarmes alm on alm.tag_id = t1.tag_id  
WHERE rnk < 10;

or With this for mysql 8.x

SELECT rnk, t1.`tag_id`, `values`,alarm
FROM
(Select 
  *
  , ROW_NUMBER() OVER (
 PARTITION BY tag_id
ORDER BY tag_id ASC) rnk
From pv_values
Where tag_id in (SELECT tag_id FROM alarmes)) t1 inner join alarmes alm on alm.tag_id = t1.tag_id  
WHERE rnk < 11;

In both cases you get

rnk     tag_id  values  alarm
0       1       value1  Alarm1
1       1       value2  Alarm1
2       1       value3  Alarm1
3       1       value4  Alarm1
4       1       value5  Alarm1
5       1       value6  Alarm1
6       1       value7  Alarm1
7       1       value8  Alarm1
8       1       value9  Alarm1
9       1       value10 Alarm1
0       2       value1  Alarm2
1       2       value2  Alarm2
2       2       value3  Alarm2
3       2       value4  Alarm2
4       2       value5  Alarm2
5       2       value6  Alarm2
6       2       value7  Alarm2
7       2       value8  Alarm2
8       2       value9  Alarm2
9       2       value10 Alarm2
0       3       value1  Alarm3
1       3       value2  Alarm3
2       3       value3  Alarm3
3       3       value4  Alarm3
4       3       value5  Alarm3
5       3       value6  Alarm3
6       3       value7  Alarm3
7       3       value8  Alarm3
8       3       value9  Alarm3
9       3       value10 Alarm3
0       4       value1  Alarm4
1       4       value3  Alarm4
2       4       value4  Alarm4
3       4       value5  Alarm4
4       4       value6  Alarm4
5       4       value7  Alarm4
6       4       value8  Alarm4
7       4       value9  Alarm4
8       4       value10 Alarm4
9       4       value11 Alarm4

As you can see it is selected 10 Rows for every tag_id.

this is made by giving all rows for every tag_id a rownumber and then select the first 10.

You have to join your table pv and ordering pv_values to your needs.

  • but the name of alarm (Alarm1,2,3) is not in alarms table it is in a table called pv so there must connection between 3 tables not only 3 – Abdula yesterday
  • 1
    Look i showed you, without any data how you can make it . If you have difficulties to adept it to your structure, please make a dbfiddle example(Which you should have made in the first place) with your data and i will see what i can do. – nbk yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.