it doesn't correlate

SELECT [Pcode], avg(convert(float,[Seq]))
FROM [DB].[dbo].[PartyTime]
WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
from [PartyTime] JOIN [PartyTime] [PT]
ON [PartyTime].[Pcode] = [PT].[Pcode]
AND [PartyTime].[Seq] = [PT].[Seq]
order by [PartyTime].[Seq])
GROUP By [Pcode]
..yields:
TOP 4.5
..but what I'm after is:
BOT 11954.5
TOP 4.5
It takes the top 8 of all of PartyTime rather than the top 8 of each
Pcode that it's averaging. Just a subselect without any correlation.
Anyone got a correct syntax for this or another approach? Remember, the
idea is not to hardcode Pcode values; the set is unknown at query time.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***SELECT [Pcode], avg(convert(float,[Seq]))
FROM [DB].[dbo].[PartyTime]
WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
from [PartyTime] JOIN [PartyTime] [PT]
ON [PartyTime].[Pcode] = [PT].[Pcode]
AND [PartyTime].[Seq] = [PT].[Seq]
Group by [PartyTime].[Seq]
order by [PartyTime].[Seq])
GROUP By [Pcode]
Another Example for the subquery:
Use northwind
Select TOP 5 CustomerID
From Orders
group by CustomerID
order by CustomerID
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M D" <mardukes@.aol.com> schrieb im Newsbeitrag
news:eykIxmoSFHA.3392@.TK2MSFTNGP12.phx.gbl...
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> ..yields:
> TOP 4.5
> ..but what I'm after is:
> BOT 11954.5
> TOP 4.5
> It takes the top 8 of all of PartyTime rather than the top 8 of each
> Pcode that it's averaging. Just a subselect without any correlation.
> Anyone got a correct syntax for this or another approach? Remember, the
> idea is not to hardcode Pcode values; the set is unknown at query time.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***|||This modification has no effect on the output except to slow the query.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime] PT INNER JOIN
(select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> Group by [PartyTime].[Seq]
> order by [PartyTime].[Seq]) SUBQuery
on SUBQuery sq = PT.sq
> WHERE [Seq] IN GROUP By [Pcode]
Use this query to keep up performace, what does the inner query bring for a
resultset ?
Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:OXx213oSFHA.1152@.tk2msftngp13.phx.gbl...
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> Group by [PartyTime].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> Another Example for the subquery:
> Use northwind
> Select TOP 5 CustomerID
> From Orders
> group by CustomerID
> order by CustomerID
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "M D" <mardukes@.aol.com> schrieb im Newsbeitrag
> news:eykIxmoSFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||the inner query returns {1,2,3,4,5,6,7,8} which all happen to be of
Pcode "TOP". Hence I get no avg for Pcode "BOT".
What I need the inner query to return is the what this hardcoded query
returns:
select top 8 [Seq] from [ProfTraining]
where Pcode = "TOP"
order by [ProfTraining].[Seq]
UNION
select top 8 [Seq] from [ProfTraining]
where Pcode = "BOT"
order by [ProfTraining].[Seq]
Again, I can't hardcode the Pcodes because the next set of data might
have other values OR I don't want to code out n UNION statements.
Interestingly, if I change it to
.. EXISTS (select top 8 * ...
I get two values but they are the avg's of the top 8's, they're avg's of
the entire groups.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||Correction:
I get two values but they AREN'T the avg's of the top 8's, they're avg's
of the entire groups.
More accurately, I need the inner query to correlate; it would look like
the UNION because the inner query would be offering the top 8 records
having the same Pcode as the outer record.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 26 Apr 2005 10:59:27 -0700, M D wrote:

>SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
>..yields:
>TOP 4.5
>..but what I'm after is:
>BOT 11954.5
>TOP 4.5
(snip)
Hi md,
Try if this one works better:
SELECT Pcode, AVG(CAST(Seq AS float))
FROM PartyTime
WHERE Seq IN (SELECT TOP 8 PT.Seq
FROM PartyTime AS PT
WHERE PT.Pcode = PartyTime.Pcode
ORDER BY PT.Seq)
GROUP BY Pcode
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||BINGO!
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||MD,
THis should work...
Select Pcode, Avg(Cast(Seq As Float))
From PartyTime Pt
Where (Select Count(*)
From PartyTime
Where PCode = Pt.PCode
And Seq >= pt.Seq) <= 8
Group By PCode
"M D" wrote:

> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> ...yields:
> TOP 4.5
> ...but what I'm after is:
> BOT 11954.5
> TOP 4.5
> It takes the top 8 of all of PartyTime rather than the top 8 of each
> Pcode that it's averaging. Just a subselect without any correlation.
> Anyone got a correct syntax for this or another approach? Remember, the
> idea is not to hardcode Pcode values; the set is unknown at query time.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***
>|||TOP 11946.5
MPAW 23573.5
I can't figure out why you think that works but it's slower and wrong.
Examine the one above for the solution.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***sql