From: esquel@sommarskog.se   
      
   Tony Johansson (johansson.andersson@telia.com) writes:   
   > In a field in the database called Cid we have a format like year-35-X   
   > So in the database we can have numbers that can look like this.   
   >  2012-35-1   
   > 2012-35-2   
   > ....   
   > 2012-35-56   
   > 2012-35-0145   
   >   
   > If I want a select statement that return the largest number for X how   
   > can I write this ?   
   > So if we have 2012-35-0194 in the database field Cid I want to get back   
   > 195 ?   
      
   RJA and Bob has already answered your question as posted, and they have   
   also hinted that you need to restructure the design. For one thing, what does   
   the existence of 2012-35-145 and 2012-35-0145 signify?   
      
   Whatever, to retrieve the MAX value of the last part, SQL Server needs to scan   
   the table. Or at least scan all entries for the year in question. Which may be   
   acceptable. Or just a plain disaster.   
      
   It seems to me that it would be better to have physical column that controls   
   this number, and you would run MAX on that column. Then you have a computed   
   column which holds the string.   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)