T-SQL Subquery Max(Date) and Joins

T-SQL Subquery Max(Date) and Joins

Try this:

Select *,
    Price = (Select top 1 Price 
             From MyPrices 
             where PartID = mp.PartID 
             order by PriceDate desc
            )
from MyParts mp

MAX Date Subquery SQL Server

You forgot about the alias Z that you specify in your subquery. If you use Assessment in your subquery, then he considers the Assessment from the outside query because Assessment has name Z in your subquery.

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]

FROM Headcount INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] = 
                 (SELECT MAX(CAST(Z.[Assessment Date] as datetime)) 
                  FROM Assessment AS Z 
                  WHERE Z.[ID] = Assessment.[ID] 
                       AND Z.[Assessment Date] <= Headcount.[Snapshot Date]
                 )

SELECT where max date in subquery

If you want the most recent salary for each employee then join the salaries table to a subquery:

SELECT e.*, s1.*
FROM employees e
LEFT JOIN salaries s1
    ON e.emp_no = s1.emp_no
INNER JOIN
(
    SELECT emp_no, MAX(from_date) AS max_from_date
    FROM salaries
    GROUP BY emp_no
) s2
    ON s1.emp_no    = s2.emp_no AND
       s1.from_date = s2.max_from_date;

Joining to MAX date record in group

In SQL Server 2005+:

SELECT  *
FROM    job j
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    jobstatus js
        WHERE   js.jobid = j.jobid
        ORDER BY
                js.date DESC
        ) js

In SQL Server 2000:

SELECT  *
FROM    job j
LEFT JOIN
        jobstatus js
ON      js.id =
        (
        SELECT  TOP 1 id
        FROM    jobstatus jsi
        WHERE   jsi.jobid = j.jobid
        ORDER BY
                jsi.date DESC
        )

These queries handle possible duplicates on Date correctly.

How to make LEFT JOIN with row having max date?

Please try the below avoiding sub query to be outer joined

SELECT
 p.id,
 b.amount
 FROM Person p
 LEFT JOIN(select * from Bill where date =
 (SELECT MAX(date) FROM Bill b1 WHERE person_id = 1)) b ON b.person_id = p.id 
 WHERE p.id = 1;

MYSQL table join with MAX(Date)

You are using the aggregation max() on date (while grouping by student)

The result for the column academic_session is therefore unpredictable IF THAT VALUE is not unique within the selected aggregation. (Run it a few hundred times, and the result may change)

MSSQL does not allow this (for reasons) - mysql allows it, and returns "something" - which might be wrong. Wrong in terms of: "That value is in no way related to your max(date) column, they only have the same student in common"

You either have to aggregate that column as well - or use a different query.

Your statement would be correct, if you consider this missing aggregation, and use group_concat to get the "possible options" for that column:

select 
student_course_registration.student,
group_concat(student_course_registration.academic_session),
max(academic_session.start_date)
from student_course_registration, academic_session  where student_course_registration.student=101366
and student_course_registration.academic_session=academic_session.id group by student_course_registration.student

but mind, that column would still be grouped by student, ignoring any relation to the start_date.

ps.: To fetch a result with the latest date: use ORDER BY date DESC LIMIT 0,1 - and you avoid all the troubles of grouping here:

select 
student_course_registration.student,
student_course_registration.academic_session,
academic_session.start_date
from student_course_registration, academic_session  where student_course_registration.student=101366
and student_course_registration.academic_session=academic_session.id
ORDER BY academic.session.start_date DESC
LIMIT 0,1

pps.: You need to use aggregations, if you would like to fetch that information for 40 students in one query. (grouping by student).

To fetch the latest information for a single student, using order and limit is sufficent (and way faster)

---

Related Topics