SQL Server Join – Selecting First Row Based on Condition

join;oraclesql server

I have 2 tables PRODUCT and MANUFACTURER

Table1: PRODUCT, Columns: PRODUCT_ID,PRODUCT_NAME

Table2: MANUFACTURER, Columns: PRODUCT_ID,MANF_ID,ITEM_NO,DEFAULT_MANF

I have to select the default manufacture of a product from the MANUFACTURER table. If no default manufacture is available, i will select a manufacturer by the ITEM_NO from the MANUFACTURER table.

I have written query for this as below:

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF 
FROM PRODUCT p 
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001' 
AND m.MANF_ID = (SELECT TOP 1 MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID and coalesce(man.DEFAULT_MANF, 'N') = 'Y' ORDER BY man.DEFAULT_MANF, man.MANF_ID )

This query works as expected in SQL Server, but on Oracle it wont work as I am using 'TOP' in the query.

Is there any way I can modify the query to work in both SQL Server and Oracle?

Best Answer

If your version of Oracle is 12 or above, you can use FETCH FIRST n ROWS ONLY (doc link):

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF 
FROM PRODUCT p 
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001' 
AND m.MANF_ID = (SELECT MANF_ID FROM MANUFACTURER man WHERE man.PRODUCT_ID = p.PRODUCT_ID and coalesce(man.DEFAULT_MANF, 'N') = 'Y' ORDER BY man.DEFAULT_MANF, man.MANF_ID FETCH FIRST 1 ROWS ONLY)

If you're on Oracle 11 or below you need to use rownum=1 instead.

I'll add that if your schema was designed properly there would only be one default manufacturer for a product, and the subquery wouldn't be needed.

To actually answer your question though, you can abuse row_number.

SELECT p.PRODUCT_ID, p.PRODUCT_NAME, m.MANF_ID, m.ITEM_NO, m.DEFAULT_MANF 
FROM PRODUCT p 
INNER JOIN MANUFACTURER m on p.PRODUCT_ID = m.PRODUCT_ID
WHERE p.PRODUCT_ID = 'PROD001' 
AND m.MANF_ID = (  
    SELECT MANF_ID FROM (
        SELECT MANF_ID, ROW_NUMBER() OVER (ORDER BY man.DEFAULT_MANF, man.MANF_ID) as RN  
        FROM MANUFACTURER man 
        WHERE man.PRODUCT_ID = p.PRODUCT_ID 
        and coalesce(man.DEFAULT_MANF, 'N') = 'Y'
    ) HACK
    WHERE RN=1
);
Related Question