SQL: Get multiple values in same Table

Question

I have a Table which contains more than 1 parameters for the same ID:

id      identifier_id   ts                  name        int_value
6117    12              2020-02-25 11:44:40 Enable      0
6118    12              2020-02-25 11:44:40 Limit1      600000
6119    12              2020-02-25 11:44:40 Limit2      800000
6115    12              2020-02-25 11:44:40 Reset       0
6116    12              2020-02-25 11:44:40 TimePeriod  604800
16648   12              2020-02-28 06:36:50 Enable      1
16778   12              2020-02-28 09:31:19 Limit1      15
16779   12              2020-02-28 09:31:19 Limit2      25

What i need is for identifier_id (here 12) i want to get Limit1 and Limit2 and the int_valuesfor these names. With the latest ts. My Result should look like this:

id      identifier_id   Limit1_value    Limit2_value
16778   12              15              25

Im searching and trying since hours but havent found anything that worked for me.

do you need id in your result ? How is it defined as one row in the result corresponds to 2 rows in the input. — Conffusion
– Conffusion, Commented Aug 19, 2020 at 14:32
is id a primary key (unique value)? How is it known that 16778 and 16779 go together? Do you need to answer this for other pairs of rows? In general, how is know that a particular pair go together? — Elroy Flynn
– Elroy Flynn, Commented Aug 19, 2020 at 14:34

Conffusion · Accepted Answer · 2020-08-19 15:03:12Z

2

Without the id column (assuming your table is named parameters):

with params as (
  select distinct identifier_id
    from parameters)
select params.identifier_id,
       (select int_value from parameters p where p.identifier_id = params.identifier_id and p.name = 'Limit1' order by p.ts desc limit 1) as Limit1,
       (select int_value from parameters p2 where p2.identifier_id = params.identifier_id and p2.name = 'Limit2' order by p2.ts desc limit 1) as Limit2
from params

answered Aug 19, 2020 at 15:03

Conffusion

4,5152 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

s0Nic Over a year ago

Thank you very much just what i needed.

Collectives™ on Stack Overflow

SQL: Get multiple values in same Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related