0

I have a table with data like the following: Name, City, LastUpdated. I want to get all the data from table distinct by name but only record where last updated is latest.
For e.g. if there are 2 records
Bill, LA, 2017-09-11
Bill, NY, 2017-09-19
In the above the query should only return Bill, NY, 2017-09-19 record. This way I want all records where name is distinct but last updated is latest (descending).

What sql query would do the job?

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Greatchap
  • 382
  • 8
  • 21

3 Answers3

3

I am posting this mainly because the other answers/comments seemed to be off from what you need here. If all you wanted to obtain were the lastest update associated with each name, you could use the following simple GROUP BY query:

SELECT Name, MAX(LastUpdated)
FROM yourTable
GROUP BY Name

But if you want to obtain the full latest record (i.e. all columns) for each name, you hit a snag because GROUP BY only allows us to select Name or another columns in an aggregate (not entirely true if the other column can be uniquely determined by Name, but true in general). A canonical way to go here would be to just join your original table to the above as a subquery, and filter off all rows except the latest one for each name.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT Name, MAX(LastUpdated) AS max_last_updated
    FROM yourTable
    GROUP BY Name
) t2
    ON t1.Name = t2.Name AND
       t1.LastUpdated = t2.max_last_updated
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
2

This can be solved in two steps:

  1. Find the row with the last_updated_date
  2. Show all data for that row.

The query could be:

SELECT
t.name,
t.city,
t.last_updated
FROM yourtable t
WHERE t.last_updated = (
  SELECT MAX(s.last_updated)
  FROM yourtable s
  WHERE s.name = t.name
);
bbrumm
  • 1,342
  • 1
  • 8
  • 13
  • Thank you. However can I replace SELECT t.name, t.city, t.last_updated with t.* here. – Greatchap Sep 19 '17 at 07:39
  • Yes, you can, but it's better practice and easier to maintain if you specify the column names rather than t.* – bbrumm Sep 20 '17 at 00:04
  • If I wish to add where clause for e.g. search on basis of name or any column and get latest record then what to do ? – Greatchap Sep 27 '17 at 05:14
  • Where do you want to filter the records? It will either go inside your subquery after WHERE s.name = t.name, or it will go after that last ) and before the ; as a "WHERE column=value". If you want to filter all fo your results, go with the WHERE clause after the bracket. If you want to filter the criteria where you find the last updated date, put it inside the subquery. – bbrumm Sep 27 '17 at 06:25
1
SELECT t1.*
FROM Tablename AS t1
(
   SELECT name, MAX(LastUpdated) AS LastUpdated
   FROM Tablename
   GROUP BY name
) AS t2 INNER JOIN t1.name = t2.name
               AND t1.LastUpdated = t2.LastUpdated