Member Avatar for jovillanuev

Guys,

Please take a look my script.
my object here is to get the most recent date from table 2.
what if the recent date from table 2 is less than the date from table 1?

Table1

Type-Date1
--------
1-2011-07-16
2-2011-07-16 
3-2011-07-10 
4-2011-07-10 

Table2

Type-Date2
--------
1-2011-07-06 
2-2011-07-07 
3-2011-07-01 
4-2011-07-01 
 
Select 
    min(Date2)
From Table1 as a
Left outer join table2 as b
on a.type = b.type
and date2 > date1


regards,
JOV
  • 2 Contributors
  • 3 Replies
  • 104 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by Pgmer

Dani AI

Generated

Two quick clarifications based on the thread: ’s join with a date condition will drop rows where Table2’s date is earlier than Table1’s, and ’s “TOP 1” idea returns only a single row for the whole result set (not one per Type). If the goal is “the most recent date per Type considering both tables,” pick the later of Table1.Date1 and the latest Table2.Date2 for that Type — or compute the max across both tables — rather than filtering Table2 with a join condition.

Here are three practical patterns (SQL Server) that do that. Use CASE to choose the later value when needed.

SELECT t1.Type,
       CASE WHEN m.MaxDate2 IS NULL OR m.MaxDate2 < t1.Date1 THEN t1.Date1 ELSE m.MaxDate2 END AS MostRecentDate
FROM Table1 t1
LEFT JOIN (
    SELECT Type, MAX(Date2) AS MaxDate2
    FROM Table2
    GROUP BY Type
) m ON m.Type = t1.Type;
SELECT t1.Type,
       CASE WHEN t2.Date2 IS NULL OR t2.Date2 < t1.Date1 THEN t1.Date1 ELSE t2.Date2 END AS MostRecentDate
FROM Table1 t1
OUTER APPLY (
    SELECT TOP (1) Date2
    FROM Table2 t2
    WHERE t2.Type = t1.Type
    ORDER BY Date2 DESC
) t2;
SELECT Type, MAX(DateVal) AS MostRecentDate
FROM (
    SELECT Type, Date1 AS DateVal FROM Table1
    UNION ALL
    SELECT Type, Date2 AS DateVal FROM Table2
) x
GROUP BY Type;

Notes: keep Date1/Date2 as proper date/datetime types (time portions affect comparisons). For performance, index Table2 on (Type, Date2) so the TOP 1 or MAX can use seeks; prefer the aggregated-join or UNION approach on large sets. If the desired business rule differs (e.g., prefer Table2 only when it is strictly later), adjust the CASE logic accordingly.

Recommended Answers

All 3 Replies

Member Avatar for Pgmer

Is your script is giving any result? i could see where condition is missing in script. you want recent date only from table 2 ? what is the relation between table1 and table 2?

Member Avatar for jovillanuev

The where clause condition is Date also. Yes from table2. the relation of table 1 and table 2 is the type.

Member Avatar for Pgmer

If your object is to get most recent date only from table 2 Why cant u just do select top 1 coulumn order by column desc?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.