0

We have a database table with structure as such:

id (PK - identity, integer)
NPI (varchar)
lastname (varchar)
firstname (varchar)
city (varchar)
state (varchar)

The nature of the table is that some NPIs are in the table twice or N times.

We are trying to select the most recent NPI (that with the max id) given the other parameters.

I could do a 

select * from NPI where id = (select max(id) from NPI where NPI = 'xxxx')

But this does not bring in the other info such as city/state.

I'd like to do something like this:

SELECT NPI from NPI where id = (select max(id) from npi where city = 'city')

but this only returns 1 row of course, the most recent NPI that is from that city.

How do I return all records from that city but only the most recent NPI?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rob
  • 2,363
  • 7
  • 36
  • 54
  • That solution was not for ms sql. Thanks though. – Rob May 15 '13 at 12:38
  • It is. I wrote it. What makes you say that? Given the tags for that are SQL Server... – gbn May 15 '13 at 12:42
  • For SQL Server 2000 (if that's what you mean) then use the MAX solution here http://dba.stackexchange.com/questions/1002/how-to-get-the-max-row/1004#1004) – gbn May 15 '13 at 12:46

0 Answers0