Hello everyone,
I am trying to write an SQL query to fetch data from the below table.
Requirement: There will be overlapping MEDICATION_ID in at least 3 MANUFACTURE_ID column. I need to rank the MEDICATIOIN_ID in the following order and select a single row per MEDICATION_ID based upon this rank. Order of MANUFACTURE_ID is 45, 48 and 50 and other MANUFACTURE_ID's can be ignored. Only the MANUFACTURE_ID 45,48 and 50 should be ranked and others are not required. If a MEDICATIONI_ID is present in all three MANUFACTURE_ID 45,48, and 50 then the rank will be 1,2 and 3 and select only rank 1. From the below table, MEDICATION_ID ‘8956’ is for MANUFACTURE_ID 45,48 and 50 and I need to get the data for row MANUFACTURE_ID 45 only because the order of the rank is 45, 48 and 50. This rank should only be for MEDICATION_ID per every MANUFACTURE_ID and there is a chance of duplicate data if we select data from MEDICATION_ID, MANUFACTURE_ID.
I am not sure if there is a way to write an SQL query to fetch only one MEDICATION_ID if the MEDICATION_ID overlaps with the MEDICATION_ID from different MANUFACTURE_ID and get only MEDICATIOIN_ID row as per the rank. As you see the expected output, for MEDICATION_ID ‘8956' we have only MANUFACTURE_ID ‘45’ record because the order is 45, 48 and 50 and we need to select only 45 MANUFACTURE_ID record. The other data should populate as is without any calculations or filters.
Sample table create statement:
CREATE TABLE CVS_LOCATION
(
MEDICATION_ID VARCHAR2(20),
MANUFACTURE_ID VARCHAR2(10),
LOCATION_NAME VARCHAR2(200),
LOCATION_STATE VARCHAR2(20)
);
Sample data:
INSERT INTO DAARJI.CVS_LOCATION VALUES('8956','45','NY','NY');
INSERT INTO DAARJI.CVS_LOCATION VALUES('8956','48','PHILLY','PA');
INSERT INTO DAARJI.CVS_LOCATION VALUES('23564','49','JERSEY CITY','NJ');
INSERT INTO DAARJI.CVS_LOCATION VALUES('8956','50','NEWARK','DE');
INSERT INTO DAARJI.CVS_LOCATION VALUES('859658','55','AUSTIN','TX');
INSERT INTO DAARJI.CVS_LOCATION VALUES('123456','44','PITTSBURGH','PA');
INSERT INTO DAARJI.CVS_LOCATION VALUES('123456','48','ALLENTOWN','PA');
INSERT INTO DAARJI.CVS_LOCATION VALUES('123564','49','ATLANTIC CITY','NJ');
INSERT INTO DAARJI.CVS_LOCATION VALUES('8956','50','DOVER','DE');
INSERT INTO DAARJI.CVS_LOCATION VALUES('8956','50','DALLAS','TX');
COMMIT;
Data in the table:
Expected Output:
<table><tbody><tr><td style="height:14.4pt;width:80pt;">MEDICATION_ID</td><td style="border-left-style:none;width:91pt;">MANUFACTURE_ID</td><td style="border-left-style:none;width:86pt;">LOCATION_NAME</td><td style="border-left-style:none;width:85pt;">LOCATION_STATE</td></tr><tr><td style="border-top-style:none;height:14.4pt;text-align:right;">123456</td><td style="border-left-style:none;border-top-style:none;text-align:right;">48</td><td style="border-left-style:none;border-top-style:none;">ALLENTOWN</td><td style="border-left-style:none;border-top-style:none;">PA</td></tr><tr><td style="border-top-style:none;height:14.4pt;text-align:right;">123456</td><td style="border-left-style:none;border-top-style:none;text-align:right;">44</td><td style="border-left-style:none;border-top-style:none;">PITTSBURGH</td><td style="border-left-style:none;border-top-style:none;">PA</td></tr><tr><td style="border-top-style:none;height:14.4pt;text-align:right;">123564</td><td style="border-left-style:none;border-top-style:none;text-align:right;">49</td><td style="border-left-style:none;border-top-style:none;">ATLANTIC CITY</td><td style="border-left-style:none;border-top-style:none;">NJ</td></tr><tr><td style="border-top-style:none;height:14.4pt;text-align:right;">23564</td><td style="border-left-style:none;border-top-style:none;text-align:right;">49</td><td style="border-left-style:none;border-top-style:none;">JERSEY CITY</td><td style="border-left-style:none;border-top-style:none;">NJ</td></tr><tr><td style="border-top-style:none;height:14.4pt;text-align:right;">859658</td><td style="border-left-style:none;border-top-style:none;text-align:right;">55</td><td style="border-left-style:none;border-top-style:none;">AUSTIN</td><td style="border-left-style:none;border-top-style:none;">TX</td></tr><tr><td style="border-top-style:none;height:14.4pt;text-align:right;">8956</td><td style="border-left-style:none;border-top-style:none;text-align:right;">45</td><td style="border-left-style:none;border-top-style:none;">NY</td><td style="border-left-style:none;border-top-style:none;">NY</td></tr></tbody></table>