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

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

Re: How do I write this sql statement

Show all headers | View raw

On Dec 8 2012, 8:50 am, Erland Sommarskog <esq...@sommarskog.se>
wrote:
> Tony Johansson (johansson.anders...@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, esq...@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

Just to follow up on Bob's point about keys:  SQL 2012 has a SEQUENCE
object that works like the SEQUENCE object in Oracle.  If you need to
know the value of a surrogate prior to insertion, that is probably the
way to go, if you are using 2012.

That said, I am not a big fan of surrogate keys.  I also agree with
Erland that the 'X' portion of the column should be on its own.  As a
general principle, column values should be atomic, meaning that
substringing a column shouldn't be required for queries.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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