sql server programming: How to rewrite this... -- sql server programming

How to rewrite this...

Faye
9/6/2007 11:18:24 PM

sql server programming: I got the following,

DECLARE @temp TABLE( rownum int identity(1, 1), Usr int, Val int )

INSERT INTO @temp( Usr, Val )
SELECT Usr, Val
FROM D
ORDER BY Usr, Val

SELECT A.Usr, AVG(1.0*A.Val) AS medianValue
FROM @temp A
INNER JOIN
( SELECT Usr, FLOOR(AVG(1.0*rownum)) AS m1,
CEILING(AVG(1.0*rownum)) AS m2
FROM @temp
GROUP BY Usr
) B
ON A.Usr = B.Usr AND A.rownum BETWEEN B.m1 AND B.m2
GROUP BY A.Usr

and, I want to rewrite it as a query without the temporary table
@temp. I thought I almost got it, but I got an error message referring
to rownum.

SELECT A.Usr, AVG(1.0 * A.Val) AS medianValue
FROM (SELECT Usr, Val
FROM dbo.D
ORDER BY Usr, Val) AS A
INNER JOIN
(SELECT Usr, FLOOR(AVG(1.0 * rownum)) AS m1, CEILING(AVG(1.0 *
rownum)) AS m2
FROM (SELECT Usr, Val FROM dbo.D AS D_1 ORDER BY Usr,
Val) AS derivedtbl_1
GROUP BY Usr) AS B
ON A.Usr = B.Usr AND A.rownum BETWEEN B.m1 AND B.m2
GROUP BY A.Usr

Can someone see what I am trying to do and help me... I am not sure
why we need rownum...

Thanks for your input.

Faye

RE: How to rewrite this...

Lars-Erik
9/7/2007 12:56:03 AM

Hi, Faye!

If you use SQL Server 2005 you can use a function called ROW_NUMBER() to get
the row number in the current query.

But why are you averaging the row number anyway? Don't you want to average
the value? As far as I can understand you don't need the row number at all..?

--
Lars-Erik

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.