filer out update records in query

[raindogs]

Here's a tricky one. I have to select from a DB that has not been especially well maintained and I need to filter the IDs. Every record in the table has a unique ID, but in some cases the record has been updated and a letter has been appended to the ID. I only want to return the most recent record. For example, if there are three records with ID numbers 10a, 10b, and 10c, I only want to return the record for 10c. I did manage to make this work by creating a couple of temporary tables, but surely there must be a cleaner way. Any suggestions?

Thanks,
Alex

 

[gmmastros]

I don't know if this is cleaner, but it doesn't involve temp tables.

Select Max(Id)
From   [!]TableName[/!]
Group By Case When PatIndex('%[a-z]%', id) = 0
              Then Id
              Else Left(Id, PatIndex('%[a-z]%', id) -1 )
              End

You really should think about splitting this in to multiple columns. Number 1 rule for databases is to store ONLY 1 piece of information in a field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[raindogs]

Thanks, I'll give that a look. Unfortunately, I'm a contractor and the company I'm working for will not allow me to make any changes to the DB structure. I just have to work with what I've got.