SQL record with latest date

5 pts.
Tags:
SQL
SQL tables
if there is one table with a id and names as cloumns and another table with the id and dates, if want to have a id, name and the latest dates as an id can have mulitple dates what would be the query
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

It would be something like this:

<pre>SELECT t1.id,t1.name,MAX(t2.date)
FROM table_1 t1 JOIN table_2 t2
ON t1.id = t2.id
GROUP BY t1.id,t1.name</pre>

——— kccrosser ———–

The answer above will work, but only if the only data desired from t2 is the date field. If you need any additional fields from t2, using GROUP BY and aggregate functions can be a pain.

I would use the following approach:

<pre>SELECT t1.id, t1.name, t2.date, t2.[otherstuff]
FROM table_1 t1
JOIN table_2 t2 ON t2.id = t1.id
WHERE NOT EXISTS (
SELECT 1 FROM table_2 t22
WHERE t22.id = t2.id AND t22.date > t2.date)</pre>

If there is a possibility that there are multiple t2 records with the same ID and Date values, then you would also want to qualify the results on some additional columns – like the primary key, e.g.:

<pre>SELECT t1.id, t1.name, t2.date, t2.[otherstuff]
FROM table_1 t1
JOIN table_2 t2 ON t2.id = t1.id
WHERE NOT EXISTS (
SELECT 1 FROM table_2 t22
WHERE t22.id = t2.id AND
(t22.date > t2.date OR (t22.date %3</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kccrosser
    (I tried three times to "improve this answer", but it errored each time... Anyway...) The answer above will work, but only if the only data desired from t2 is the date field. If you need any additional fields from t2, using GROUP BY and aggregate functions can be a pain. I would use the following approach: SELECT t1.id, t1.name, t2.date, t2.[otherstuff] FROM table_1 t1 JOIN table_2 t2 ON t2.id = t1.id WHERE NOT EXISTS ( SELECT 1 FROM table_2 t22 WHERE t22.id = t2.id AND t22.date > t2.date) If there is a possibility that there are multiple t2 records with the same ID and Date values, then you would also want to qualify the results on some additional columns - like the primary key, e.g.: SELECT t1.id, t1.name, t2.date, t2.[otherstuff] FROM table_1 t1 JOIN table_2 t2 ON t2.id = t1.id WHERE NOT EXISTS ( SELECT 1 FROM table_2 t22 WHERE t22.id = t2.id AND (t22.date > t2.date OR (t22.date = t2.date AND t22. > t2.)))
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: