Ask : sql query for my case

Other Tech Topics
1

my data on database

account,number,value,counter
xxxx , 1 , 10000,1
null , null , 500000, 2
null , null , 10000 , 3

can sql query do result something like this :

account,number,value,counter
xxxx , 1 , 10000 ,1
xxxx , 1 , 500000 , 2
xxxx , 1 , 10000 , 3

can anyone help me. thanks

2

let me try to understand it correctly, you trying to replace null in account column with xxxx and any null in number column to be 1?

3

yes thats true, but without update query,

account,number,value,counter
xxxx , 1 , 10000,1
null , null , 500000, 2
null , null , 10000 , 3
yyyy, 5 ,400000, 4
null , null , 50000 , 5

result something like this :

account,number,value,counter
xxxx , 1 , 10000 ,1
xxxx , 1 , 500000 , 2
xxxx , 1 , 10000 , 3
yyyy, 5 ,400000, 4
yyyy, 5 , 50000 , 5

4 
SELECT account = coalesce(d.account, p.account),
	number,
	value,
	counter
FROM data d
OUTER APPLY (
	SELECT TOP 1 x.account
	FROM data x
	WHERE x.counter < d.counter
		AND x.account IS NOT NULL
	ORDER BY x.counter DESC
	) p
5

@khtan thank you, your query working well (mssql), but if i try run on interactive sql sybase there is an error "incorrect syntax near 'OUTER' ",

6

I am not familiar with Sybase and SQLTeam.com is on MS SQL Server, For Sybase, try posting at a Sybase forum.

7

@khtan im very thankful. :blush: