Can I nest a select statement within an IF function in SQL?-CodePudding

I want to write a query that joins table 1 and table 2 on item code to the location in table 2. There are multiple locations per item code and potentially multiple item code entries per location depending on date. I'm only interested in the most recent item per location. To achieve this I've used a nested query to select the max date per both location and item number. I'm still returning more rows of data than anticipated and suspect it is due to some duplicate locations slipping through, potentially with two different item numbers.

I'm wondering if its possible to use the IF operator to say "If there are duplicate locations, choose the location with the more recent date" Is this possible?

Here is what I have written so far:

SELECT t1.item_no, t1.date, t2.location, t2.date
FROM table 1 t1
JOIN table 2 t2 ON t1.item_no = t2.item_no
WHERE (t1.item_no, t1.date) IN
    ( 
     SELECT item_no, MAX(date)
     FROM table 1
     GROUP BY item_no
    )
AND (t2.location, t2.date) IN
    (
     SELECT location, MAX(date)
     FROM table 2
     GROUP BY location
    )

CodePudding user response：

Change your query and use Subquery

SELECT t1.item_no, t1.date, t2.location, t2.date FROM 
    ( 
     SELECT item_no, MAX(date) date
     FROM table 1
     GROUP BY item_no
    ) T1
    JOIN
      (
     SELECT location, MAX(date) date
     FROM table 2
     GROUP BY location
    ) T2
    ON t1.item_no = t2.location

CodePudding user response：

Without knowing DBMS, a solution could be to use ROW_NUMBER(). I'm not sure if there's a preference for nested queries over say CTE but a solution w/ CTE could be:

WITH items AS (
    SELECT
        item_no
        ,date AS item_date
        ,row_number() OVER (PARTITION BY item_no ORDER BY date desc) as rn  
    FROM table1
),

locations AS (
    SELECT
        location
        ,item_no
        ,date AS location_date
        ,ROW_NUMBER() OVER(PARTITION BY item_no, location ORDER BY date desc) as rn
    from table2
)

SELECT
    t1.item_no
    ,t1.item_date
    ,t2.location
    ,t2.location_date
FROM items AS t1
JOIN locations AS t2 on t1.item_no = t2.item_no
    AND t2.rn = 1
WHERE t1.rn = 1