I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the store
d
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but tha
t
still doesn't explain why QA works fine) and it still hangs. Any ideas?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1
>|||cbrichards via droptable.com wrote:
> I have a rather complicated query statement in a stored proc which all of
a
> sudden hangs in production. I copy the same database to QA and run the sto
red
> proc and it executes in less than a second. The explain plans are differen
t
> between production and QA. I have recompiled (sp_recompile) the stored pro
c
> in production. Still hangs. I have run sp_updatestats in production (but t
hat
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:[vbcol=seagreen]
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>
>[quoted text clipped - 6 lines]
Message posted via http://www.droptable.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another pos
t
you responded too.
Tracy McKibben wrote:
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> The indexes are the same as I copied the database to QA. I have another po
st
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a respons
e
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via droptable.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I a
m
> still stumped over the lack of affect of DBReindex on my indexes another p
ost
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 3
0
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about
30
> seconds into running (It should complete in less than a half second), I ge
t
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy
on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Showing posts with label complicated. Show all posts
Showing posts with label complicated. Show all posts
Wednesday, March 7, 2012
Query Timeout
I have a rather complicated query statement in a stored proc which all of a
sudden hangs in production. I copy the same database to QA and run the stored
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but that
still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||cbrichards via SQLMonster.com wrote:
> I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the stored
> proc and it executes in less than a second. The explain plans are different
> between production and QA. I have recompiled (sp_recompile) the stored proc
> in production. Still hangs. I have run sp_updatestats in production (but that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>>I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the
>[quoted text clipped - 6 lines]
>> that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via http://www.sqlmonster.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another post
you responded too.
Tracy McKibben wrote:
>> I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the stored
>> proc and it executes in less than a second. The explain plans are different
>> between production and QA. I have recompiled (sp_recompile) the stored proc
>> in production. Still hangs. I have run sp_updatestats in production (but that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> The indexes are the same as I copied the database to QA. I have another post
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a response
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via SQLMonster.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I am
> still stumped over the lack of affect of DBReindex on my indexes another post
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 30
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about 30
> seconds into running (It should complete in less than a half second), I get
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sudden hangs in production. I copy the same database to QA and run the stored
proc and it executes in less than a second. The explain plans are different
between production and QA. I have recompiled (sp_recompile) the stored proc
in production. Still hangs. I have run sp_updatestats in production (but that
still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Double-check the indexes on the production and QA boxes first to make sure
they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
production if the indexes are the same.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:64ebbf94f87e0@.uwe...
>I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the
> stored
> proc and it executes in less than a second. The explain plans are
> different
> between production and QA. I have recompiled (sp_recompile) the stored
> proc
> in production. Still hangs. I have run sp_updatestats in production (but
> that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1
>|||cbrichards via SQLMonster.com wrote:
> I have a rather complicated query statement in a stored proc which all of a
> sudden hangs in production. I copy the same database to QA and run the stored
> proc and it executes in less than a second. The explain plans are different
> between production and QA. I have recompiled (sp_recompile) the stored proc
> in production. Still hangs. I have run sp_updatestats in production (but that
> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>
When the query "hangs", check sysprocesses to see what's blocking it...
My guess is your production server has more activity on it than the QA
server, and something is blocking your query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The indexes are the same as I copied the database to QA. I have another post
on this website that Tracy has been responding to about the DBREindex not
having any affect on my fragmented index. But there has not been a response
on that issue since posting my SHOWCONTIG results.
Mike C# wrote:
>Double-check the indexes on the production and QA boxes first to make sure
>they are the same. Also try rebuilding indexes (DBCC DBREINDEX) in
>production if the indexes are the same.
>>I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the
>[quoted text clipped - 6 lines]
>> that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
--
Message posted via http://www.sqlmonster.com|||Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
running has not revealed any blocking. Initially I suspected that too. I am
still stumped over the lack of affect of DBReindex on my indexes another post
you responded too.
Tracy McKibben wrote:
>> I have a rather complicated query statement in a stored proc which all of a
>> sudden hangs in production. I copy the same database to QA and run the stored
>> proc and it executes in less than a second. The explain plans are different
>> between production and QA. I have recompiled (sp_recompile) the stored proc
>> in production. Still hangs. I have run sp_updatestats in production (but that
>> still doesn't explain why QA works fine) and it still hangs. Any ideas?
>When the query "hangs", check sysprocesses to see what's blocking it...
> My guess is your production server has more activity on it than the QA
>server, and something is blocking your query.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> The indexes are the same as I copied the database to QA. I have another post
> on this website that Tracy has been responding to about the DBREindex not
> having any affect on my fragmented index. But there has not been a response
> on that issue since posting my SHOWCONTIG results.
>
Actually there was a response from another poster, stating that the
index in question is too small to defragment. I didn't respond because
his answer is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||cbrichards via SQLMonster.com wrote:
> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
> running has not revealed any blocking. Initially I suspected that too. I am
> still stumped over the lack of affect of DBReindex on my indexes another post
> you responded too.
>
There must be some clue in sysprocesses. Is there a waittype shown for
the query? Does the execution plan reveal any clues?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||When I run "select * from master.dbo.sysprocesses" while the proc is about 30
seconds into running (It should complete in less than a half second), I get
the following from sysprocesses:
blocked = 0
lastwaittype = PAGELATCH_SH
CPU = 261204
When I run it in production (and times out) the explain plain provided in
profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
QA does not show in its explain plan.
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||At about 20 minutes into the proc execution I ran sysprocesses again:
Blocked = 0 (in fact all the rows for the blocked column from sysprocesses
are zero)
LastWaitType = LCK_M_S
Tracy McKibben wrote:
>> Evaluating master.dbo.sysprocesses and running sp_who2 while the proc is
>> running has not revealed any blocking. Initially I suspected that too. I am
>> still stumped over the lack of affect of DBReindex on my indexes another post
>> you responded too.
>There must be some clue in sysprocesses. Is there a waittype shown for
>the query? Does the execution plan reveal any clues?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1|||cbrichards via SQLMonster.com wrote:
> When I run "select * from master.dbo.sysprocesses" while the proc is about 30
> seconds into running (It should complete in less than a half second), I get
> the following from sysprocesses:
> blocked = 0
> lastwaittype = PAGELATCH_SH
> CPU = 261204
> When I run it in production (and times out) the explain plain provided in
> profiler shows a Hash join and two Bookmark lookups that the "exact" copy on
> QA does not show in its explain plan.
>
For the two bookmark lookups, what index is being used? Are they the
same indexes that the query uses in QA? If not, you need to determine
why it's choosing different indexes. Is the amount of data the same
between QA and prod? Try forcing the index using an index hint, see if
that improves the performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Monday, February 20, 2012
Query table for cheapest items (but a little more complicated than that)
I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.
tblWine tblSources
ID Name ID WineID Source Price Status
------ -------------
1 Le Dome 1 1 Smith 100.00 IB
2 Teyssier 2 1 Jones 110.00 IB
3 Muscat 3 1 Hill 100.00 DP
4 2 Smith 135.00 DP
5 2 Hill 125.00 DP
I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.
tblWineSources
WineID Name SourceID Source Price Status
-------------------
1 Le Dome 1 Smith 110.00 IB
1 Le Dome 3 Hill 100.00 DP
2 Teyssier 5 Hill 125.00 DPPlease check your Resultsets they do not represent the requirement you stated...
BTW, for your help ...
CREATE TABLE tblWine(
ID VARCHAR(10)
,NAME VARCHAR(20))
Go
CREATE TABLE tblSources(
ID VARCHAR(10)
,WINEID VARCHAR(10)
,SOURCE VARCHAR(20)
,PRICE INT
,STATUS VARCHAR(10))
Go
INSERT INTO tblWine VALUES('1','Le Dome')
INSERT INTO tblWine VALUES('2','Teyssier')
INSERT INTO tblWine VALUES('3','Muscat')
INSERT INTO tblSources VALUES('1','1','Smith',100.00,'IB')
INSERT INTO tblSources VALUES('2','1','Jones',110.00,'IB')
INSERT INTO tblSources VALUES('3','1','Hill',100.00,'DP')
INSERT INTO tblSources VALUES('4','2','Smith',135.00,'DP')
INSERT INTO tblSources VALUES('5','2','Hill',125.00,'DP')
GO
--The required Query Place--
SELECT tblSources.WINEID
,tblWine.Name
,tblSources.ID as SourceID
,tblSources.Source
,tblSources.Status
,tblSources.Price as Maxprice
FROM tblWine
INNER JOIN tblSources
ON tblWine.ID=tblSources.WINEID
GROUP BY tblSources.WINEID
,tblWine.Name
,tblSources.ID
,tblSources.Source
,tblSources.Status
,tblSources.Price
----------
Go
DROP TABLE tblWine
DROP TABLE tblSources|||:) I realise that. That's because I don't want to return all the rows in the join. I only need the rows that have the cheapest price for their status. Since my first post I've managed to solve part of the problem.
SELECT
tblWine.ID AS WineID,
tblWine.Name,
tblSources.ID AS SourceID,
tblSources.Source,
tblSources.Status,
tblSources.Price
FROM
tblSources
LEFT OUTER JOIN tblWine ON tblSources.WineID = tblWine.ID
GROUP BY
tblWine.ID,
tblWine.Name,
tblSources.ID,
tblSources.Source,
tblSources.Status,
tblSources.Price
HAVING
tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'IB'
ORDER BY
subSources.Price ASC
)
OR tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'DP'
ORDER BY
subSources.Price ASC
)
ORDER BY
tblWine.ID ASC,
tblSources.ID ASC
Thanks for you help though.
The problem now is that I've made these tables simpler for the purpose of this post so not to confuse the issue. There is actually another column in tblSources which I need to check is unique just like status. This additional column can have 1 of 8 different values, and the only way I currently know how to do it is so expand the query above to accommodate 16 subqueries in the HAVING clause, which doesn't seem ideal, or is it?
So ...
tblWine tblSources
ID Name ID WineID Source Price Status Format
------ -------------
1 Le Dome 1 1 Smith 100.00 IB Bottle
2 Teyssier 2 1 Jones 110.00 IB Bottle
3 Muscat 3 1 Hill 100.00 DP Bottle
4 2 Smith 135.00 DP Bottle
5 2 Hill 125.00 DP Magnum
... would return ...
tblWineSources
WineID Name SourceID Source Price Status Format
-------------------
1 Le Dome 1 Smith 110.00 IB Bottle
1 Le Dome 3 Hill 100.00 DP Bottle
2 Teyssier 5 Smith 135.00 DP Bottle
2 Teyssier 5 Hill 125.00 DP Magnum
So because there are two sources for Le Dome / IB / Bottle, only the cheapest record would be shown.|||If no one can help with the above, could someone tell me if the following is even possible?
How do I find the IDs of the cheapest Format/Status pair?
So ...
tblSources
ID Price Format Status
-----------
1540 100.00 Bottles IB
1541 110.00 Bottles DP
1542 105.00 Bottles IB
1543 105.00 Bottles DP
1544 115.00 Magnums IB
1545 110.00 Magnums IB
... would result in ...
Results
ID
--
1540
1543
1545
I could use a query for each combonation but the actual data can contain 1 of 2 different statuses or 1 of 8 different formats.|||try this as the solution to your problem in post #1select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? yep, that is correct
One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.which source?|||any source, doesn't really matter. but as long as only one is shown.|||thanks to you, i've sussed it!
here's an example of what i did (i used a specific wine with many sources, in this case it had an ID of 11)
SELECT
tblSources.*
FROM
tblSources
WHERE
tblSources.WineID = 11
AND tblSources.ID =
(
SELECT
TOP 1
subSources.ID
FROM
tblSources AS subSources
WHERE
subSources.WineID = tblSources.WineID
AND subSources.Format = tblSources.Format
AND subSources.Status = tblSources.Status
AND subSources.CasePrice =
(
SELECT
MIN(sub2Sources.CasePrice)
FROM
tblSourcesAS sub2Sources
WHERE
sub2Sources.WineID = subSources.WineID
AND sub2Sources.Format = subSources.Format
AND sub2Sources.Status = subSources.Status
)
)|||select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )
and S.Source =
( select min(Source)
from tblSources
where WineID = S.WineID
and Status = S.Status
and Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status ) )
tblWine tblSources
ID Name ID WineID Source Price Status
------ -------------
1 Le Dome 1 1 Smith 100.00 IB
2 Teyssier 2 1 Jones 110.00 IB
3 Muscat 3 1 Hill 100.00 DP
4 2 Smith 135.00 DP
5 2 Hill 125.00 DP
I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.
tblWineSources
WineID Name SourceID Source Price Status
-------------------
1 Le Dome 1 Smith 110.00 IB
1 Le Dome 3 Hill 100.00 DP
2 Teyssier 5 Hill 125.00 DPPlease check your Resultsets they do not represent the requirement you stated...
BTW, for your help ...
CREATE TABLE tblWine(
ID VARCHAR(10)
,NAME VARCHAR(20))
Go
CREATE TABLE tblSources(
ID VARCHAR(10)
,WINEID VARCHAR(10)
,SOURCE VARCHAR(20)
,PRICE INT
,STATUS VARCHAR(10))
Go
INSERT INTO tblWine VALUES('1','Le Dome')
INSERT INTO tblWine VALUES('2','Teyssier')
INSERT INTO tblWine VALUES('3','Muscat')
INSERT INTO tblSources VALUES('1','1','Smith',100.00,'IB')
INSERT INTO tblSources VALUES('2','1','Jones',110.00,'IB')
INSERT INTO tblSources VALUES('3','1','Hill',100.00,'DP')
INSERT INTO tblSources VALUES('4','2','Smith',135.00,'DP')
INSERT INTO tblSources VALUES('5','2','Hill',125.00,'DP')
GO
--The required Query Place--
SELECT tblSources.WINEID
,tblWine.Name
,tblSources.ID as SourceID
,tblSources.Source
,tblSources.Status
,tblSources.Price as Maxprice
FROM tblWine
INNER JOIN tblSources
ON tblWine.ID=tblSources.WINEID
GROUP BY tblSources.WINEID
,tblWine.Name
,tblSources.ID
,tblSources.Source
,tblSources.Status
,tblSources.Price
----------
Go
DROP TABLE tblWine
DROP TABLE tblSources|||:) I realise that. That's because I don't want to return all the rows in the join. I only need the rows that have the cheapest price for their status. Since my first post I've managed to solve part of the problem.
SELECT
tblWine.ID AS WineID,
tblWine.Name,
tblSources.ID AS SourceID,
tblSources.Source,
tblSources.Status,
tblSources.Price
FROM
tblSources
LEFT OUTER JOIN tblWine ON tblSources.WineID = tblWine.ID
GROUP BY
tblWine.ID,
tblWine.Name,
tblSources.ID,
tblSources.Source,
tblSources.Status,
tblSources.Price
HAVING
tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'IB'
ORDER BY
subSources.Price ASC
)
OR tblSources.ID IN
(
SELECT
TOP 1
subSources.ID
FROM
tblSources subSources
WHERE
subSources.WineID = tblWine.ID
AND subSources.Status = 'DP'
ORDER BY
subSources.Price ASC
)
ORDER BY
tblWine.ID ASC,
tblSources.ID ASC
Thanks for you help though.
The problem now is that I've made these tables simpler for the purpose of this post so not to confuse the issue. There is actually another column in tblSources which I need to check is unique just like status. This additional column can have 1 of 8 different values, and the only way I currently know how to do it is so expand the query above to accommodate 16 subqueries in the HAVING clause, which doesn't seem ideal, or is it?
So ...
tblWine tblSources
ID Name ID WineID Source Price Status Format
------ -------------
1 Le Dome 1 1 Smith 100.00 IB Bottle
2 Teyssier 2 1 Jones 110.00 IB Bottle
3 Muscat 3 1 Hill 100.00 DP Bottle
4 2 Smith 135.00 DP Bottle
5 2 Hill 125.00 DP Magnum
... would return ...
tblWineSources
WineID Name SourceID Source Price Status Format
-------------------
1 Le Dome 1 Smith 110.00 IB Bottle
1 Le Dome 3 Hill 100.00 DP Bottle
2 Teyssier 5 Smith 135.00 DP Bottle
2 Teyssier 5 Hill 125.00 DP Magnum
So because there are two sources for Le Dome / IB / Bottle, only the cheapest record would be shown.|||If no one can help with the above, could someone tell me if the following is even possible?
How do I find the IDs of the cheapest Format/Status pair?
So ...
tblSources
ID Price Format Status
-----------
1540 100.00 Bottles IB
1541 110.00 Bottles DP
1542 105.00 Bottles IB
1543 105.00 Bottles DP
1544 115.00 Magnums IB
1545 110.00 Magnums IB
... would result in ...
Results
ID
--
1540
1543
1545
I could use a query for each combonation but the actual data can contain 1 of 2 different statuses or 1 of 8 different formats.|||try this as the solution to your problem in post #1select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.|||But wouldn't that mean that if their were two rows that had the same price and status, then they would both be included? yep, that is correct
One requirement which I've forgot to include is that if two rows have the same price and status but are from two different sources, then only one row would be shown.which source?|||any source, doesn't really matter. but as long as only one is shown.|||thanks to you, i've sussed it!
here's an example of what i did (i used a specific wine with many sources, in this case it had an ID of 11)
SELECT
tblSources.*
FROM
tblSources
WHERE
tblSources.WineID = 11
AND tblSources.ID =
(
SELECT
TOP 1
subSources.ID
FROM
tblSources AS subSources
WHERE
subSources.WineID = tblSources.WineID
AND subSources.Format = tblSources.Format
AND subSources.Status = tblSources.Status
AND subSources.CasePrice =
(
SELECT
MIN(sub2Sources.CasePrice)
FROM
tblSourcesAS sub2Sources
WHERE
sub2Sources.WineID = subSources.WineID
AND sub2Sources.Format = subSources.Format
AND sub2Sources.Status = subSources.Status
)
)|||select W.ID as WineID
, W.Name
, S.ID as SourceID
, S.Source
, S.Price
, S.Status
from tblWine as W
inner
join tblSources as S
on S.WineID = W.ID
and S.Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status )
and S.Source =
( select min(Source)
from tblSources
where WineID = S.WineID
and Status = S.Status
and Price =
( select min(Price)
from tblSources
where WineID = S.WineID
and Status = S.Status ) )
Subscribe to:
Posts (Atom)