0

I'm new to Microsoft SQL server and am working with an existing database. I need to query a table to get a list of customers. Each customer has a CustID and a HistoryID. Certain edits to a customer record (within a desktop application) cause a new row to get written to the table, with the history ID incremented e.g.:

| CustID | HistoryID | Name        | City    |
| AB1    | 1         | Bob Smith   | Bangkok |
| CD2    | 1         | Joe Bloggs  | London  |
| CD2    | 2         | Joe Bloggs  | Paris   |
| ZA9    | 1         | Sarah Jones | Berlin  |

I need to get the "latest" record for each customer (the one with the highest HistoryID) - so in the example above, I'd to get the row for "Bob Smith", the second row for "Joe Bloggs" (not the first) and the row for "Sarah Jones".

What is the best query to do this? Thanks

gbuckingham89
  • 377
  • 2
  • 14
  • 1
    Possibly a duplicate of https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql – Jay Buckman Jun 16 '17 at 11:19

1 Answers1

1

Use ROW_NUMBER():

select t.*
from (select t.*,
             row_number() over (partition by custid order by historyid desc) as seqnum
      from t
     ) t
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786