Ours is a windows based application.
When we open the application the CPU usage is 0% and the Memory Usage
is 54,324Kb
When I open a specific form in a module, the CPU usage is 0% and the
Memory Usage increases accordingly (67,730 Kb).
Now when I lock and unlock the System the CPU usage increases to 50%
with the Memory Usage being 67,730 kb.
I have made use of a .Net memory profiler and had taken snapshots
before locking and unlocking the system.
The following is the result after comparing both the versions.
NameSpace Name Total New Removed Delta Total
Max Min Delta
System WeakRefrence 1681 613 0 613 26896
16 16 9808
System.Reflection RunTimeMethodInfo 1229 8
0 8 29496 24 24 192
System.Windows.Forms NativeMethods.TracKMOU.. 10
5 0 5 240 24 24 120
System Runtimetype 2725 4 0 4 4360
16 16 64
System String 37232 5 1 4 2256 102456
18 458
Can anybody let me know the reason for the increase in CPU usage and
how I can overcome this.
And why is there a drastic increase in the number of instances for
WeakReference class when the operation is just locking and unlocking
and nothing to do with the application.
Thanks in advance
sowmyaIs this question related to SQL Server?. If so, please explain what you
mean by locking/unlocking. Perhaps you intended to post to a .Net forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<sowmya.rangineni@.gmail.com> wrote in message
news:1182142975.732292.25730@.m36g2000hse.googlegroups.com...
> Ours is a windows based application.
> When we open the application the CPU usage is 0% and the Memory Usage
> is 54,324Kb
> When I open a specific form in a module, the CPU usage is 0% and the
> Memory Usage increases accordingly (67,730 Kb).
>
> Now when I lock and unlock the System the CPU usage increases to 50%
> with the Memory Usage being 67,730 kb.
>
> I have made use of a .Net memory profiler and had taken snapshots
> before locking and unlocking the system.
>
> The following is the result after comparing both the versions.
>
> NameSpace Name Total New Removed Delta Total
> Max Min Delta
> System WeakRefrence 1681 613 0 613 26896
> 16 16 9808
> System.Reflection RunTimeMethodInfo 1229 8
> 0 8 29496 24 24 192
> System.Windows.Forms NativeMethods.TracKMOU.. 10
> 5 0 5 240 24 24 120
> System Runtimetype 2725 4 0 4 4360
> 16 16 64
> System String 37232 5 1 4 2256 102456
> 18 458
>
> Can anybody let me know the reason for the increase in CPU usage and
> how I can overcome this.
>
> And why is there a drastic increase in the number of instances for
> WeakReference class when the operation is just locking and unlocking
> and nothing to do with the application.
> Thanks in advance
> sowmya
>
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Incorrect SET options all of a sudden?
We've been running a SQL Server based application for some time (Access
front-end). Suddenly, the application is reporting an error when running a
stored procedure to insert a new row in a specific table; there's an Exec
statement doing it. Here is the error:
--
Insert failed because the following SET options have incorrect settings:
ANSI_nulls
Quoted_identifier
Arith abort
--
Does anyone know what could have changed in SQL Server to cause this? We can
add a new record manually through Enterprise Manager. Thanks!!Is it possible that something in the app changed that issues different set
statments? Use SQL Profiler to take a look at what's being sent...
or... could it be that someone recompiled the procedure with different set
options in effect?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time (Access
> front-end). Suddenly, the application is reporting an error when running a
> stored procedure to insert a new row in a specific table; there's an Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this? We
can
> add a new record manually through Enterprise Manager. Thanks!!
>|||Hi,
I don't know if its possible. This is a production system that started to
exhibit this behaviour in the middle of the day. There's just one developer,
but he always works on a separate development database (same server
however). We'll keep checking. Any additional ideas are welcome.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:#TX7M5ydDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Is it possible that something in the app changed that issues different set
> statments? Use SQL Profiler to take a look at what's being sent...
> or... could it be that someone recompiled the procedure with different set
> options in effect?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> > We've been running a SQL Server based application for some time (Access
> > front-end). Suddenly, the application is reporting an error when running
a
> > stored procedure to insert a new row in a specific table; there's an
Exec
> > statement doing it. Here is the error:
> >
> > --
> > Insert failed because the following SET options have incorrect settings:
> > ANSI_nulls
> > Quoted_identifier
> > Arith abort
> > --
> >
> > Does anyone know what could have changed in SQL Server to cause this? We
> can
> > add a new record manually through Enterprise Manager. Thanks!!
> >
> >
>|||Perhaps someone created an index on a computed column or on a view that
references the table. This will require that the options listed be
turned on during update operations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time
(Access
> front-end). Suddenly, the application is reporting an error when
running a
> stored procedure to insert a new row in a specific table; there's an
Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect
settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this?
We can
> add a new record manually through Enterprise Manager. Thanks!!
>sql
front-end). Suddenly, the application is reporting an error when running a
stored procedure to insert a new row in a specific table; there's an Exec
statement doing it. Here is the error:
--
Insert failed because the following SET options have incorrect settings:
ANSI_nulls
Quoted_identifier
Arith abort
--
Does anyone know what could have changed in SQL Server to cause this? We can
add a new record manually through Enterprise Manager. Thanks!!Is it possible that something in the app changed that issues different set
statments? Use SQL Profiler to take a look at what's being sent...
or... could it be that someone recompiled the procedure with different set
options in effect?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time (Access
> front-end). Suddenly, the application is reporting an error when running a
> stored procedure to insert a new row in a specific table; there's an Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this? We
can
> add a new record manually through Enterprise Manager. Thanks!!
>|||Hi,
I don't know if its possible. This is a production system that started to
exhibit this behaviour in the middle of the day. There's just one developer,
but he always works on a separate development database (same server
however). We'll keep checking. Any additional ideas are welcome.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:#TX7M5ydDHA.2524@.TK2MSFTNGP09.phx.gbl...
> Is it possible that something in the app changed that issues different set
> statments? Use SQL Profiler to take a look at what's being sent...
> or... could it be that someone recompiled the procedure with different set
> options in effect?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> > We've been running a SQL Server based application for some time (Access
> > front-end). Suddenly, the application is reporting an error when running
a
> > stored procedure to insert a new row in a specific table; there's an
Exec
> > statement doing it. Here is the error:
> >
> > --
> > Insert failed because the following SET options have incorrect settings:
> > ANSI_nulls
> > Quoted_identifier
> > Arith abort
> > --
> >
> > Does anyone know what could have changed in SQL Server to cause this? We
> can
> > add a new record manually through Enterprise Manager. Thanks!!
> >
> >
>|||Perhaps someone created an index on a computed column or on a view that
references the table. This will require that the options listed be
turned on during update operations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:eubl7uydDHA.3232@.TK2MSFTNGP10.phx.gbl...
> We've been running a SQL Server based application for some time
(Access
> front-end). Suddenly, the application is reporting an error when
running a
> stored procedure to insert a new row in a specific table; there's an
Exec
> statement doing it. Here is the error:
> --
> Insert failed because the following SET options have incorrect
settings:
> ANSI_nulls
> Quoted_identifier
> Arith abort
> --
> Does anyone know what could have changed in SQL Server to cause this?
We can
> add a new record manually through Enterprise Manager. Thanks!!
>sql
Wednesday, March 7, 2012
Including NULL Option in WHERE Clause
Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
Thanks
I think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks
|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
[vbcol=seagreen]
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...[vbcol=seagreen]
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
>
>
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
Thanks
I think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks
|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
[vbcol=seagreen]
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...[vbcol=seagreen]
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
>
>
Including NULL Option in WHERE Clause
Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statemen
t
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a fiel
d
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statem
ent
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THE
N null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CAS
E
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the seco
nd
> returns TRUE.
> I would further like to be able to construct this solution or any other th
at
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
[vbcol=seagreen]
> I think you're looking for the ISNULL function. It would be used like thi
s:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
>|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...[vbcol=seagreen]
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts th
e
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the origin
al
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
>
>
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statemen
t
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a fiel
d
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statem
ent
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THE
N null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CAS
E
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the seco
nd
> returns TRUE.
> I would further like to be able to construct this solution or any other th
at
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
[vbcol=seagreen]
> I think you're looking for the ISNULL function. It would be used like thi
s:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
>|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...[vbcol=seagreen]
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts th
e
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the origin
al
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
>
>
Including NULL Option in WHERE Clause
Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
> > Hi,
> >
> > I'm trying to create predicates that will work the same way whether a field
> > contains a blank or a null value. This is based on the fact that nulls get
> > converted to blanks when loaded into a VS control, then loaded into the
> > e.Values or e.OldValues arrays when deleting or updating the current row,
> > respectively, in a VS/C# FormView.
> >
> > At the point where the query is constructed, I cannot tell whether the
> > original value was a null or not, so I attempted to use a CASE WHEN statement
> > in my WHERE clause like the following:
> >
> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> > ELSE '' END) ...
> >
> > However, this doesn't work because the when [Address] is null, the CASE
> > statement returns a null which results in the predicate containing:
> >
> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >
> > and that is not the same as:
> >
> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >
> > the first returns FALSE event though [Address] is null, while the second
> > returns TRUE.
> >
> > I would further like to be able to construct this solution or any other that
> > works in a generic method that can be called by any query to construct its
> > predicate for each field where this situation is a possibility.
> >
> > Thanks|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>> I think you're looking for the ISNULL function. It would be used like
>> this:
>> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
>> "WJB" wrote:
>> > Hi,
>> >
>> > I'm trying to create predicates that will work the same way whether a
>> > field
>> > contains a blank or a null value. This is based on the fact that nulls
>> > get
>> > converted to blanks when loaded into a VS control, then loaded into the
>> > e.Values or e.OldValues arrays when deleting or updating the current
>> > row,
>> > respectively, in a VS/C# FormView.
>> >
>> > At the point where the query is constructed, I cannot tell whether the
>> > original value was a null or not, so I attempted to use a CASE WHEN
>> > statement
>> > in my WHERE clause like the following:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
>> > null
>> > ELSE '' END) ...
>> >
>> > However, this doesn't work because the when [Address] is null, the CASE
>> > statement returns a null which results in the predicate containing:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = null) ...
>> >
>> > and that is not the same as:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
>> >
>> > the first returns FALSE event though [Address] is null, while the
>> > second
>> > returns TRUE.
>> >
>> > I would further like to be able to construct this solution or any other
>> > that
>> > works in a generic method that can be called by any query to construct
>> > its
>> > predicate for each field where this situation is a possibility.
>> >
>> > Thanks|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> > Not exactly. I had tried that already. The problem is that the field
> > itself
> > actually could contain either null or blank. The ISNULL function as below
> > works if the field is blank but not if it is null. I need a solution that
> > works for both. Essentially, it needs to be a test that uses one value if
> > true and another if false. The problem is that "= null" in SQL Server is
> > not
> > the same as "is null". If the ISNULL function had an overload that would
> > return a value if false and would equate to "IS NULL" if true, that would
> > work.
> >
> > Can you suggest any other options?
> >
> > Thanks
> >
> > "Code Wench" wrote:
> >
> >> I think you're looking for the ISNULL function. It would be used like
> >> this:
> >>
> >> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> >>
> >> "WJB" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to create predicates that will work the same way whether a
> >> > field
> >> > contains a blank or a null value. This is based on the fact that nulls
> >> > get
> >> > converted to blanks when loaded into a VS control, then loaded into the
> >> > e.Values or e.OldValues arrays when deleting or updating the current
> >> > row,
> >> > respectively, in a VS/C# FormView.
> >> >
> >> > At the point where the query is constructed, I cannot tell whether the
> >> > original value was a null or not, so I attempted to use a CASE WHEN
> >> > statement
> >> > in my WHERE clause like the following:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
> >> > null
> >> > ELSE '' END) ...
> >> >
> >> > However, this doesn't work because the when [Address] is null, the CASE
> >> > statement returns a null which results in the predicate containing:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >> >
> >> > and that is not the same as:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >> >
> >> > the first returns FALSE event though [Address] is null, while the
> >> > second
> >> > returns TRUE.
> >> >
> >> > I would further like to be able to construct this solution or any other
> >> > that
> >> > works in a generic method that can be called by any query to construct
> >> > its
> >> > predicate for each field where this situation is a possibility.
> >> >
> >> > Thanks
>
>
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
> > Hi,
> >
> > I'm trying to create predicates that will work the same way whether a field
> > contains a blank or a null value. This is based on the fact that nulls get
> > converted to blanks when loaded into a VS control, then loaded into the
> > e.Values or e.OldValues arrays when deleting or updating the current row,
> > respectively, in a VS/C# FormView.
> >
> > At the point where the query is constructed, I cannot tell whether the
> > original value was a null or not, so I attempted to use a CASE WHEN statement
> > in my WHERE clause like the following:
> >
> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> > ELSE '' END) ...
> >
> > However, this doesn't work because the when [Address] is null, the CASE
> > statement returns a null which results in the predicate containing:
> >
> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >
> > and that is not the same as:
> >
> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >
> > the first returns FALSE event though [Address] is null, while the second
> > returns TRUE.
> >
> > I would further like to be able to construct this solution or any other that
> > works in a generic method that can be called by any query to construct its
> > predicate for each field where this situation is a possibility.
> >
> > Thanks|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>> I think you're looking for the ISNULL function. It would be used like
>> this:
>> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
>> "WJB" wrote:
>> > Hi,
>> >
>> > I'm trying to create predicates that will work the same way whether a
>> > field
>> > contains a blank or a null value. This is based on the fact that nulls
>> > get
>> > converted to blanks when loaded into a VS control, then loaded into the
>> > e.Values or e.OldValues arrays when deleting or updating the current
>> > row,
>> > respectively, in a VS/C# FormView.
>> >
>> > At the point where the query is constructed, I cannot tell whether the
>> > original value was a null or not, so I attempted to use a CASE WHEN
>> > statement
>> > in my WHERE clause like the following:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
>> > null
>> > ELSE '' END) ...
>> >
>> > However, this doesn't work because the when [Address] is null, the CASE
>> > statement returns a null which results in the predicate containing:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = null) ...
>> >
>> > and that is not the same as:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
>> >
>> > the first returns FALSE event though [Address] is null, while the
>> > second
>> > returns TRUE.
>> >
>> > I would further like to be able to construct this solution or any other
>> > that
>> > works in a generic method that can be called by any query to construct
>> > its
>> > predicate for each field where this situation is a possibility.
>> >
>> > Thanks|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> > Not exactly. I had tried that already. The problem is that the field
> > itself
> > actually could contain either null or blank. The ISNULL function as below
> > works if the field is blank but not if it is null. I need a solution that
> > works for both. Essentially, it needs to be a test that uses one value if
> > true and another if false. The problem is that "= null" in SQL Server is
> > not
> > the same as "is null". If the ISNULL function had an overload that would
> > return a value if false and would equate to "IS NULL" if true, that would
> > work.
> >
> > Can you suggest any other options?
> >
> > Thanks
> >
> > "Code Wench" wrote:
> >
> >> I think you're looking for the ISNULL function. It would be used like
> >> this:
> >>
> >> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> >>
> >> "WJB" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to create predicates that will work the same way whether a
> >> > field
> >> > contains a blank or a null value. This is based on the fact that nulls
> >> > get
> >> > converted to blanks when loaded into a VS control, then loaded into the
> >> > e.Values or e.OldValues arrays when deleting or updating the current
> >> > row,
> >> > respectively, in a VS/C# FormView.
> >> >
> >> > At the point where the query is constructed, I cannot tell whether the
> >> > original value was a null or not, so I attempted to use a CASE WHEN
> >> > statement
> >> > in my WHERE clause like the following:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
> >> > null
> >> > ELSE '' END) ...
> >> >
> >> > However, this doesn't work because the when [Address] is null, the CASE
> >> > statement returns a null which results in the predicate containing:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >> >
> >> > and that is not the same as:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >> >
> >> > the first returns FALSE event though [Address] is null, while the
> >> > second
> >> > returns TRUE.
> >> >
> >> > I would further like to be able to construct this solution or any other
> >> > that
> >> > works in a generic method that can be called by any query to construct
> >> > its
> >> > predicate for each field where this situation is a possibility.
> >> >
> >> > Thanks
>
>
Friday, February 24, 2012
Include ID field in GROUP BY statement
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruits
group by FruitName
It'll correctly return:
FruitName Cost
Apple 0.63
Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruits
group by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID FruitName Cost
2 Apple 0.63
4 Pear 0.89
Thanks.
I don't think you can do that with the ID field because there are multiple Ids for each item while using the group by. Can you use a Having clause? ie
select Cost,FruitName From Fruits
group by FruitName
having max(Cost)
This is a shot in the dark as i did not actually try your data. Hope it works though :)
|||
Thanks but dont think that would work as the 'HAVING' as i see it is just used to filter the results of the query so you'd use it if for example you wanted to only show max fuit more than ￡0.10
select Cost,FruitName From Fruit
group by FruitName,Cost
having max(Cost) > 0.1
Thats how i see it (could be wrong). But it still doesnt handle the problem of the ID.
|||Hi,
This is a round about way, but it works
Select
FruitName,cost,(Select IDfrom Fruits fWhere f.Cost=frt.cost)as IDfrom(
select FruitName,max(cost)as costFrom Fruitsgroup
by FruitName) frt|||Hi TheGrox,
The below query may help you.
select id,fruitname,costfrom (select row_number()over (partitionby fruitnameorder by costdesc)as rownum,id,fruitname,costfrom Fruits) Fwhere rownum = 1
The main trick here is using the ROW_NUMBER() OVER (PARTITION BY...) clause
This is very like getting top n records from a table belonging to each category. You can follow the article athttp://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx for more details and a sample for ROW_NUMBER() OVER (PARTITION BY...)
Eralper
http://www.kodyaz.com
|||
If you are using SQL 2000 you can try this:
Select t1.FruitName,t2.cost, Idfrom Fruits t1join (select FruitName,max(cost)as costFrom Fruitsgroup by FruitName ) t2on t1.FruitName = t2.fruitnameand t1.Cost = t2.cost|||
Here is another one:
select t1.*from fruitsas t1where t1.ID= (select top 1 t2.IDfrom fruitsas t2where t2.FruitName = t1.FruitNameorder by t2.costDESC)|||
Limno
I like your query better than mine. I am joining on name and cost. If there are multiple records with same name and costs, the join could return multiple records for same "Fruit" which might be incorrect.
|||I learned that solution from Umachandar Jayachandran. He is super.
Subscribe to:
Posts (Atom)