Sql query that returns previous row to current row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sql query that returns previous row to current row

Author

Topic

cgfl_dubai
Starting Member

3 Posts

Posted - 2006-06-21 : 03:13:43

hi,

HAve to run this as a single quey in sql server 2000.
I have got a query which has to return the previous row data to the current row if its value is null

ex:

Prod Id. Name Qty
1 AAA 10
2 BBB Null
3 CCC Null
4 DDD 20
5 EEE 20

So in this case the result should be

Prod Id. Name Qty
1 AAA 10
2 BBB 10
3 CCC 10
4 DDD 20
5 EEE 20

Thanx in advance !

Cheers,
Pari.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-21 : 03:18:51

[code]select ProdID, Name, coalesce(Qty,
(select top 1 Qty from table x where Qty is not null and x.ProdID < t.ProdID order by x.ProdID desc))
from table t
[/code]

cgfl_dubai
Starting Member

3 Posts

Posted - 2006-06-21 : 04:14:54

Hi many thanks, it works at its best provided the id should be unique !!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-21 : 04:27:49

quote:
Originally posted by cgfl_dubai

Hi many thanks, it works at its best provided the id should be unique !!!

Is the Prod ID unique in your case ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-21 : 04:49:47

Why do you want to do this?
Do you use any front end application to show the data?

Madhivanan

Failing to plan is Planning to fail

cgfl_dubai
Starting Member

3 Posts

Posted - 2006-06-21 : 05:28:17

I tried to test in the sample one, in that the prod id was not unique

after changing the test data to unique it worked !

suneil23
Starting Member

4 Posts

Posted - 2006-06-25 : 02:23:39

hello guys i couldn understand wat ur trying to say..pls lemme know wat x and t stand for in the query written above...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-25 : 03:02:08

quote:
Originally posted by suneil23

hello guys i couldn understand wat ur trying to say..pls lemme know wat x and t stand for in the query written above...

x and t are table alias.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-26 : 02:14:07

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources