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

Question

Using Teradata.. 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
    )

[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Mar 31 '22 at 19:56
In addition, create a https://dbfiddle.uk/ with some sample data that illustrates the problem and update your post with the url. — Lennart - Slava Ukraini, Mar 31 '22 at 20:02
Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — philipxy, May 03 '22 at 22:07
[What is the XY problem?](https://meta.stackexchange.com/q/66377/266284) PS "If there are duplicate locations, choose the location with the more recent date" just means "choose the location with the more recent date". Thinking relationally means thinking in terms of sets of values satisfying conditions. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) PS What did your research show? [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, May 03 '22 at 22:15

score 0 · Answer 1 · answered Mar 31 '22 at 20:12

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

score 0 · Answer 2 · answered Mar 31 '22 at 20:15

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

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

2 Answers2