Question 89709 in PostgreSQL Question posted in
The official documentation can be found here.

Get Max data against a ts and total count of record in single join query – POSTGRES – Postgresql

I have 2 tables with a foreign key. I have to get the counts of all the records in right table and the threshold value of max ts in right table.

TABLE alarm

column Datatype
id PK
name varchar

TABLE alarm_data

column Datatype
alarm_id FK
ts timestamp
value int

I have to get total count of alarm_data associated with an alarm_id and the value of alarm_data with latest ts.

The expected output is

alarm_id ts occurance_count
1 123456 2

Queries I have used is
https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/7351

It is always returning multiple records as I have to add alarm_data.value in group by. It should only return 1 record with value and occurence count.

Tags:

2

Answers


  1. 0

    You can do it using group by to get count() and max(ts):

      select a.id as alarm_id, max(ad.ts) as ts, count(1) as occurance_count
  from alarm a
  inner join alarm_data as ad on a.id = ad.alarm_id
  group by a.id
    Login or Signup to reply.
  2. 0

    You could use the row_number function to get the value of alarm_data with the latest ts, and the count window function to get the total count of alarm_data associated with an alarm_id.

    select A.id, 
       A.name,
       D.ts,
       D.cn as occurance_count,
       D.value
from alarm A join 
(
  select *, count(*) over (partition by alarm_id) cn,
     row_number() over (partition by alarm_id order by ts desc) rn
 from alarm_data
) D
on A.id = D.alarm_id
where D.rn = 1

    See demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search