skip to Main Content

I have two related tables as below.

Master table:

ID Date Rev
1 2022-01-01 1
2 2022-01-02 1
3 2022-01-02 2
4 2022-01-03 1

Detail table:

ID hour Capacity
1 1 1
1 2 2
1 3 3
1 4 4
2 1 5
2 2 6
2 3 7
2 4 8
3 1 9
3 2 10
3 3 11
3 4 12
4 1 13
4 2 14
4 3 15
4 4 16

The capacity for each day will be saved multiple times as revision. For some day there would be 1 revision, some other day 3 based on number of times data saved.

Now I need to take data from these two table in a single query with largest revision of each day. Expecting 12 rows of data for the 3 existing days in the chosen month.

I wrote the below query

select a1.wdcm_date as wdcm_date, c1.wdcd_block_no as wdcd_block_no, c1.wdcd_capacity as wdcd_capacity, 
      c1.wdcd_approval as wdcd_approval, a1.wdcm_revision_no as wdcm_revision_no
from wb_declared_capacity_master a1, wb_declared_capacity_detail c1
where a1.wdcm_internal_id = c1.wdcd_ref_id  and to_char(a1.wdcm_date,'MM yyyy')='01 2022' 
and wdcm_revision_no = (select max(wdcm_revision_no) from wb_declared_capacity_master where to_char(wdcm_date,'MM yyyy')='01 2022')

This returns only the data with revision number 3 dates.

My expected result should be

Date hour Capacity Rev
2022-01-01 1 1 1
2022-01-01 2 2 1
2022-01-01 3 3 1
2022-01-01 4 4 1
2022-01-02 1 9 2
2022-01-02 2 10 2
2022-01-02 3 11 2
2022-01-02 4 12 2
2022-01-03 1 13 1
2022-01-03 2 14 1
2022-01-03 3 15 1
2022-01-03 4 16 1

How to get all 12 rows (all hours for each qualifying revisions)?

2

Answers


  1. Your sub query only returns one revision number, not a different number for each date.

    What you need is a ‘correlated’ sub query, where the sub query refers to a source value in the outer query…

    Also, really do not use , for joins, which has been replace with ANSI syntax since 1992!

    Finally, don’t use meaningless aliases such as a1, c1, etc. It makes code harder to read, not easier. Aliases should have meaning.

    SELECT
       cm.wdcm_date as wdcm_date,
       cd.wdcd_block_no as wdcd_block_no,
       cd.wdcd_capacity as wdcd_capacity, 
       cd.wdcd_approval as wdcd_approval,
       cm.wdcm_revision_no as wdcm_revision_no
    FROM
       wb_declared_capacity_master   AS cm
    INNER JOIN
       wb_declared_capacity_detail   AS cd
          ON cd.wdcd_ref_id = cm.wdcm_internal_id 
    WHERE
       to_char(cm.wdcm_date,'MM yyyy')='01 2022' 
       AND
       cm.wdcm_revision_no = (
          SELECT MAX(wdcm_revision_no)
            FROM wb_declared_capacity_master
           WHERE wdcm_date = cm.wdcm_date
       ) 
    
    Login or Signup to reply.
  2. Yet another case that’s simpler and faster with DISTINCT ON:

    SELECT m.date, d.hour, d.capacity, m.rev
    FROM  (
       SELECT DISTINCT ON (date)    -- simple & fast
              id, date, rev
       FROM   master
       WHERE  date >= '2022-01-01'  -- ! sargable = faster
       AND    date <  '2022-01-02'  -- !
       ORDER  BY date, rev DESC
       ) m
    LEFT   JOIN detail d USING (id)
    ORDER  BY date, hour;
    

    See:

    And avoid expressions on columns before filtering like the plague.
    to_char(date,'MM yyyy') = '01 2022') is hugely expensive. It has to be computed for every row in the table before your filter can be applied. Use a "sargable" filter. Much faster, especially with applicable index, but even without. Related:

    Subtle detail: I use LEFT JOIN to preserve every qualifying day in the result, even if detail are missing. May or may not be possible with your data model. But won’t hurt in any case.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search