Posted by Jens on 11/03/05 12:19

Hi Kali,

create table #Tempone
(
SomeID INT,
SomeValue REAL
)


INSERT INTO #Tempone
SELECT 10 , 193.51

INSERT INTO #Tempone
SELECT 10 , 194.5

INSERT INTO #Tempone
SELECT 10 , 202.71


INSERT INTO #Tempone
SELECT 20 , 192.79

INSERT INTO #Tempone
SELECT 20 , 197.6

INSERT INTO #Tempone
SELECT 20 , 192.9


INSERT INTO #Tempone
SELECT 30 , 192.76

INSERT INTO #Tempone
SELECT 30 , 191.91

INSERT INTO #Tempone
SELECT 30 , 187.9


--Query
SELECT T1.SomeId, T1.SomeValue, ((SELECT TOP 1 SomeValue FROM #Tempone
T2 WHERE T1.SomeId = T2.SomeID AND T2.SomeValue > T1.SomeValue ORDER BY
SomeValue ) / SomeValue-1)*100
>From #Tempone T1
order by T1.SomeValue


but you should consider the order of the inserted rows, I saw that you
didn´t have an order of the value, so in this scenario they will be
fetched as they will come in to the query processor.


HTH, jens Suessmeyer.

[Back to original message]