I have two tables that I am joining together with several where clauses. However the table I am trying to join has several million rows of data. I want to find the the most recent readdate for each premnum from the pm_dmd_hist table.
My Current SQL Code is:
SELECT *
FROM pm_dmd_hist
INNER JOIN pm_premise
ON pm_dmd_Hist.premnum = pm_premise.premnum
WHERE PM_PREMISE.DIVISION_CODE = Any ('PSCO', 'DM', 'No', 'SE', 'SW', 'BD', 'NR', 'HL', 'HP', 'WD', 'FR', 'MD', 'PD', 'SV', 'UD')
AND PM_DMD_HIST.TARIFFCLASS = (6037)
AND PM_DMD_HIST.BILLED_DMD < 11
and PM_DMD_HIST.READDATE > '01-JAN-17'
Order BY readdate asc;
Without consumable test data it is difficult to tell exactly what you want.
One approach is to use CROSS APPLY. Something like:
SELECT * --use column list
FROM pm_premise P
CROSS APPLY
(
SELECT TOP (1) * --use column list
FROM pm_dmd_hist H
WHERE H.premnum = P.premnum
AND H.TARIFFCLASS = 6037
AND H.BILLED_DMD < 11
AND H.READDATE > '20170101' -- use ISO date strings
ORDER BY H.READDATE DESC
) X
WHERE P.DIVISION_CODE IN ('PSCO', 'DM', 'No', 'SE', 'SW', 'BD', 'NR', 'HL', 'HP', 'WD', 'FR', 'MD', 'PD', 'SV', 'UD');
Thank you! I think this is pretty close but I am getting an error at line 6 stating "missing expression". I am using Oracle SQL Dev if that matters at all. I can try to throw together some test data as well.
This is a MS SQL Server site so you will be better off asking Oracle questions on an Oracle site.
From memory, Oracle does not support TOP. Something like the following should work but it may not be particularly efficent:
WITH OrderedHist
AS
(
SELECT * --use column list
,ROW_NUMBER() OVER (PARITITON BY premnum ORDER BY READDATE DESC) AS rn
FROM pm_dmd_hist
WHERE TARIFFCLASS = 6037
AND BILLED_DMD < 11
AND READDATE > '20170101'
)
SELECT * --use column list
FROM pm_premise P
JOIN OrderedHist H
ON P.premnum = H.premnum
WHERE H.rn = 1
AND P.DIVISION_CODE IN ('PSCO', 'DM', 'No', 'SE', 'SW', 'BD', 'NR', 'HL', 'HP', 'WD', 'FR', 'MD', 'PD', 'SV', 'UD');