max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.
You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>
|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>
|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>
|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> I need to find the highest number in one column. Here is a catch, the
> column
>> type is varchar and some of the rows will have non number character in
>> it.
> I
>> am trying to find the highest number ignoring any row that is not a
> numeric
>> value. Here is my query.
>>
>> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> Thanks for any input.
>>
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:
> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> >
> > You may want to cast the column as an int within the max
> >
> >
> > SELECT max(cast(item_abrv as int)) as mx from item where
> > isnumeric(item_abrv) = 1
> >
> > "UGH" <nospam@.noSPam.com> wrote in message
> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> >> I need to find the highest number in one column. Here is a catch, the
> > column
> >> type is varchar and some of the rows will have non number character in
> >> it.
> > I
> >> am trying to find the highest number ignoring any row that is not a
> > numeric
> >> value. Here is my query.
> >>
> >>
> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> >>
> >> Thanks for any input.
> >>
> >>
> >
> >
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>> I tired that and I got an error. its said converting the varchar value
>> '100ISBN' to a column of data type int. I just need the query to leave
>> out
>> any field that has non numeric characters in it.
>> Thanks.
>> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
>> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>> >
>> > You may want to cast the column as an int within the max
>> >
>> >
>> > SELECT max(cast(item_abrv as int)) as mx from item where
>> > isnumeric(item_abrv) = 1
>> >
>> > "UGH" <nospam@.noSPam.com> wrote in message
>> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> >> I need to find the highest number in one column. Here is a catch, the
>> > column
>> >> type is varchar and some of the rows will have non number character in
>> >> it.
>> > I
>> >> am trying to find the highest number ignoring any row that is not a
>> > numeric
>> >> value. Here is my query.
>> >>
>> >>
>> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> >>
>> >> Thanks for any input.
>> >>
>> >>
>> >
>> >
>>

max value of table column

Hi all,
Could anyboyd tell me how i can get the max value of a table column.
A column in my table contains numeric values. in my table footer i
want to display
the max value of that column.
my table itself has one group. This group is called GroupByHour and is
simply a group by hour for all 24 hours in a day (from 00h until 23h).
In the specific column of each line i calculate a value.
It is the max value of those calculated values that i want in the
footer of this column.
I don't know if i explained myself clearly enough, so here is an
example:
hour | ColA | ColB | ColC | ColD
---
00 15 14 16 16
01
02
03 20 23 21 23
...
23 75 16 55 75
----
90 51 114 75
In the tablefooter of ColA, ColB and ColC you have the sumed value for
the columns.
The footer of ColD displays the max value of ColD.
So, what expression or function do i have use to get the max value of
ColD in my table footer?
Greetings
VinnieWon't the "Max( Fields!ColD.Value )" work?
There is a Max for 2 numbers, and a max for column values. Look in 'Common
Functions -> Operators'
//Andrew
> Hi all,
> Could anyboyd tell me how i can get the max value of a table column.
> A column in my table contains numeric values. in my table footer i
> want to display
> the max value of that column.
> my table itself has one group. This group is called GroupByHour and is
> simply a group by hour for all 24 hours in a day (from 00h until 23h).
> In the specific column of each line i calculate a value.
> It is the max value of those calculated values that i want in the
> footer of this column.
> I don't know if i explained myself clearly enough, so here is an
> example:
> hour | ColA | ColB | ColC | ColD
> ---
> 00 15 14 16 16
> 01
> 02
> 03 20 23 21 23
> ...
> 23 75 16 55 75
> ----
> 90 51 114 75
> In the tablefooter of ColA, ColB and ColC you have the sumed value for
> the columns.
> The footer of ColD displays the max value of ColD.
> So, what expression or function do i have use to get the max value of
> ColD in my table footer?
> Greetings
> Vinni|||On Aug 8, 6:24 pm, Andrew Backer <awbac...@.gmail.com> wrote:
> Won't the "Max( Fields!ColD.Value )" work?
> There is a Max for 2 numbers, and a max for column values. Look in 'Common
> Functions -> Operators'
> //Andrew
>
> > Hi all,
> > Could anyboyd tell me how i can get the max value of a table column.
> > A column in my table contains numeric values. in my table footer i
> > want to display
> > the max value of that column.
> > my table itself has one group. This group is called GroupByHour and is
> > simply a group by hour for all 24 hours in a day (from 00h until 23h).
> > In the specific column of each line i calculate a value.
> > It is the max value of those calculated values that i want in the
> > footer of this column.
> > I don't know if i explained myself clearly enough, so here is an
> > example:
> > hour | ColA | ColB | ColC | ColD
> > ---
> > 00 15 14 16 16
> > 01
> > 02
> > 03 20 23 21 23
> > ...
> > 23 75 16 55 75
> > ----
> > 90 51 114 75
> > In the tablefooter of ColA, ColB and ColC you have the sumed value for
> > the columns.
> > The footer of ColD displays the max value of ColD.
> > So, what expression or function do i have use to get the max value of
> > ColD in my table footer?
> > Greetings
> > Vinnie- Hide quoted text -
> - Show quoted text -
Hi Andrew,
I am aware of the Max function, but i cannot specify the column.
ColD is just a title of the column. In fact the name for the column is
TableColumn15
But if i do =max(Fields!TableColumn15.value) i get an error returned :
"Error 1 [rsFieldReference] The Value expression for the textbox
'textbox168' refers to the
field 'TableColumn15'. Report item expressions can only refer to
fields within the current data set scope or,
if inside an aggregate, the specified data set scope."
Is there a way to say to RS that you want the max value of all values
in a specific column.
I cannot calculate this value in my footer because the group contains
24 max values (one for each hour),
and in the report footer i would have the max value of the total of
the group (which i don't need of course).
So .. anybody have some more info on this ?
Greetings
Vinnie