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

Issue: How to convert a database field from varchar to datetime..?

Can anyone help me on this!
I've got more than a 1000 records in a SQL server database.
The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)
How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it.
thanks in advance!

You should be able to just convert them to datetime select cast(varDateField as dateTime) from yourTable. The only problem you will have is if you dont have date fields in any of the data.

Nick