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)?
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
)