Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has same field name calcuated_price has max value is 3000.

Nishith

SELECTMAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNERJOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECTCASE

WHENMAX(P.iPlanid)>MAX(AP.iPlanid)THENMAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

ENDAS maxvalue

FROM

PLANS P

INNERJOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen

Complex SQL Query - Joins, Max, Union

How to find maximum value from two tables have the same field name?

For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has

same field name calcuated_price has max value is 3000.

Nishith

SELECT MAX(P.iPlanid),MAX(AP.iPlanid)

FROM PLANS P INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||Hello Steve,
This query is fine, but it will return 2 values, I need just one max. value from 2 tables.|||

use northwind
select * into #product1 from products where productid<50
select * into #product2 from products where productid >=50

select case
when a.price>b.price then a.price
when a.price<b.price then b.price
end as maxprice

from (
select max(unitprice)price from #product1) as a
cross join
(
select max(unitprice)as price from #product2)
as b

|||

SELECT CASE

WHEN MAX(P.iPlanid) > MAX(AP.iPlanid) THEN MAX(P.iPlanid)

ELSE

MAX(AP.iPlanid)

END AS maxvalue

FROM

PLANS P

INNER JOIN ANOTHERPLANS AP

ON P.iPlanid = AP.iPlanid

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount 1;

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0;

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

There are a lot of different ways to do this, here is another:

set rowcount

select max(field_name) from table1
union
select max(field_name) from table2 order by 1 desc

set rowcount 0

Hope this Helps,

Roberto Hernandez-Pou
http://community.rhpconsulting.net

|||

Well, there are three things to discuss here. First off this statement:

Table -1 has field calcuated_price ... then Table -2 has same field name calcuated_price

This just shouts out "design issue" Of course it is totally out of context, so these may be quite different things you have modeled and you just want to compare their prices. The point I am trying to make is: if the tables have the same things in them, or even common columns that have the same meaning, then you ought to consider making one table from the common values.

Two: calulated_price sounds like you are storing an aggregate. This is generally a bad idea. As in all things it is very dependent on how the data is used, but it is usually so much more work to keep aggregates in proper sync that it is just best to calculate them as needed

Three: I live in a glass house myself, so even if your answer is: "I know, but this is what I have and can't change it" which is the case for so many database developers I come in contact with, here is what I would do:

Union the two sets first in a CTE or derived table, then treat the set as a single table. With this you can then do aggregates on theml, and groups as needed:

select groupColumn, max(calculate_price)
from (select columns, calculated_price from [table -1]
union all --I am guessing if there is overlap in column values you will want them
select columns, calculated_price from [table -2]) as tableThatShouldHaveBeen

Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K1) What is the table referenced in the lock?
2) It could be caused by lots of compiles' :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005'
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles' :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)?
> SQL 2000 or 2005'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> > K- Hide quoted text -
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can see
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Yep it's a sproc and the blocking head does constantly change.. ..what causes
this behaviour?
Thanks in advance
K
"YPD" wrote:
> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> >
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> >
> > K
>
>|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:
>> Is 736291420 an object id for a stored proc? It sounds like what MS calls
>> "rolling block". Does the blocking head spid(s) constantly changing?
>> Did you run profiler trace to see where the SP:Recompile event occurs and
>> what Event Subclass it falls into?
>>
>> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>> >
>> > We've recently been experiencing problems locking issues seemingly
>> > caused
>> > by
>> > compiles.. ..cpu max's out at 100%, query duration get longer, and we
>> > see
>> > a
>> > large number of LCK_M_X in sysprocesses with coupled with something
>> > like
>> > TAB:
>> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
>> >
>> > K
>>

Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB
:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K1) What is the table referenced in the lock?
2) It could be caused by lots of compiles' :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005'
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles' :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)
?
> SQL 2000 or 2005'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
>
>
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can se
e
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Yep it's a sproc and the blocking head does constantly change.. ..what cause
s
this behaviour?
Thanks in advance
K
"YPD" wrote:

> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...[vbcol=seagreen]
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:
>