Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1366

Re: How do I write this sql statement

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How do I write this sql statement "Tony Johansson" <johansson.andersson@telia.com> - 2012-12-08 11:44 +0100
  Re: How do I write this sql statement rja.carnegie@gmail.com - 2012-12-08 03:37 -0800
    Re: How do I write this sql statement "Tony Johansson" <johansson.andersson@telia.com> - 2012-12-08 14:31 +0100
      Re: How do I write this sql statement "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-12-08 09:15 -0500
  Re: How do I write this sql statement Erland Sommarskog <esquel@sommarskog.se> - 2012-12-08 16:50 +0100
    Re: How do I write this sql statement bill <billmaclean1@gmail.com> - 2013-01-28 22:43 -0800

csiph-web