SQL record with latest date
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
Software/Hardware used:
Software/Hardware used:
ASKED:
April 27, 2010 4:01 PM
UPDATED:
April 28, 2010 7:38 PM
Answer Wiki:
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>
To see all answers submitted to the Answer Wiki: View Answer History.
(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.)))