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

Groups > comp.databases.ms-sqlserver > #1362 > unrolled thread

How do I write this sql statement

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1362 — How do I write this sql statement

Hello!

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 
?

//Tony 

[toc] | [next] | [standalone]

#1363

On Saturday, 8 December 2012 10:44:31 UTC, Tony Johansson  wrote:
> Hello!
>
> 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 
> 
> ?

It looks as though CAST(SUBSTRING(Cid, 9, 4) AS int) will extract 
the number if the data is valid and always that length.

If the specification is "after the second/last hyphen" with variable 
length then something more elaborate is needed, and, again, it
depends on whether the data is reliably of that format; some 
simple designs will give either an error or a wrong answer if 
the data doesn't conform.  

Also, if you need to read that data, to generate a new key value,
this may be not a very good way to store it.  But sometimes you
don't have the option of using the best design.

[toc] | [prev] | [next] | [standalone]

#1364

Hello!

If I use just this
SELECT        SUBSTRING(CId, 9, 4)
FROM            cases
I get all the number for example
1001
1002
1003
...
1130 and so on

But I want the max value from these. I need to use the substring,cast and 
max but doesn't know how

It's something like this
SELECT        max(cast(SUBSTRING(CId, 9, 4) AS test) as Int))
FROM            cases

//Tony

<rja.carnegie@gmail.com> skrev i meddelandet 
news:3ee6ef1c-1eaf-4fab-b27d-2aadd8c99c1a@googlegroups.com...
> On Saturday, 8 December 2012 10:44:31 UTC, Tony Johansson  wrote:
>> Hello!
>>
>> 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
>>
>> ?
>
> It looks as though CAST(SUBSTRING(Cid, 9, 4) AS int) will extract
> the number if the data is valid and always that length.
>
> If the specification is "after the second/last hyphen" with variable
> length then something more elaborate is needed, and, again, it
> depends on whether the data is reliably of that format; some
> simple designs will give either an error or a wrong answer if
> the data doesn't conform.
>
> Also, if you need to read that data, to generate a new key value,
> this may be not a very good way to store it.  But sometimes you
> don't have the option of using the best design. 

[toc] | [prev] | [next] | [standalone]

#1365

Tony Johansson wrote:
> Hello!
>
> If I use just this
> SELECT        SUBSTRING(CId, 9, 4)
> FROM            cases
> I get all the number for example
> 1001
> 1002
> 1003
> ...
> 1130 and so on
>
> But I want the max value from these. I need to use the substring,cast
> and max but doesn't know how
>
> It's something like this
> SELECT        max(cast(SUBSTRING(CId, 9, 4) AS test) as Int))
> FROM            cases
>
You're close. You just need to get rid of the alias inside the expression:
SELECT        max(cast(SUBSTRING(CId, 9, 4)) as Int)) as maxvalue

If you're trying to get the next key value, you need to be wary of 
multi-user activity. Two users running this at the same time will get the 
same answer. If that's you're goal, let us know and we'll have some 
solutions for you.

This could be made more foolproof - it will fail when the X portion exceeds 
4 digits, right? You can take advantage of a little-known function called 
parsename(), which accepts a string containing up to 4 portions separated by 
periods. It's intended to be used to parse object names in 
server.database.schema.object format, but you can use it for strings with 
other delimiters by using the replace() function to replace the delimiters 
with periods. In your case it would look like this:

SELECT MAX(CAST(PARSENAME(REPLACE(CId,'-','.'),1) AS INT)) AS maxvalue

[toc] | [prev] | [next] | [standalone]

#1366

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

[toc] | [prev] | [next] | [standalone]

#1382

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.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web