Finding a single row for an identity

Question

I have a table that has multiple values for an identity, and I want to find just one row for each identity where the date field is the newest and then if there is still duplicates just select the one with the lowest id.

My table looks kine of like this:

UniqueID | CustomerId | Name | Address | InspDate
1            1           Bob    123       2013/08/05 00:00:00
2            1           Bob    123       2013/08/05 00:00:00
3            1           Bob    123       2013/03/01 00:00:00

So I only want the row with uniqueid of 1 to show up to show up in this example.

Also I want to limit it to only Customers with inspdates done within the last year if that is possible.

score 0 · Answer 1 · answered Jan 29 '14 at 22:56

SELECT t.*
FROM
  yourtable t INNER JOIN (
    SELECT MIN(UniqueID) as Min_ID
    FROM
      yourtable t1 INNER JOIN (SELECT CustomerID, MAX(InspDate) Max_date
                               FROM yourtable
                               GROUP BY CustomerID) m
      ON t1.CustomerID=m.CustomerID AND t1.InspDate=m.Max_date
    GROUP BY t1.CustomerID) mi
  ON t.UniqueID = mi.Min_ID

Please see fiddle here.

Finding a single row for an identity

1 Answers1