logic

# logic

• Wednesday, February 13, 2013 7:19 PM

hi

i have values in column like this

340

12

10

15

i need to go each value and substract from each and whatever max i need put in another column

example:

column1          column2

340               330

12               2

10

15

if we table first 340 ,

340-340 =0

340-12 = 328

340-10 = 330

340 -15 325

so the max for 340 is 330.

now for 12

12 - 340 : - 328

12 - 12  0

12- 10  2

12-15  -3

so the max for 12 is 2

how can i implement this logic in sql using cursor or any other way

### All Replies

• Wednesday, February 13, 2013 8:07 PM

If you want to combine each row in a table with all rows in another table (even itself), a cross join could be used.  The query below does that for your sample data set and adds a row_number column to help identify the max difference for each row.

```declare @data as table (
row_id tinyint primary key identity,
row_val smallint
)

insert into @data(row_val)
values (340),(12),(10),(15)

select
d.row_id, d.row_val,
d2.row_val as second_row_val,
d.row_val - d2.row_val as row_val_difference,
row_number() over(partition by d.row_val order by d.row_val - d2.row_val desc) as row_val_difference_position
from @data d
cross join @data d2
order by 2, 4 desc```

Results:

A filter could be performed on that row number or you could perform an aggregation.  Kinda ugly, but it may be a starting point for what you need.

website: jontav.com | Blog

• Wednesday, February 13, 2013 8:08 PM

see if this helps..
```create table SID ( id int)
insert into SID
values (100),(50),(78),(25)
GO

with CTE AS
(Select id,Row_number() Over( ORder by id  desc) as row1
from SID)

select A.id,(SELECT MIN( id ) as ID FROM CTE B where A.Id<>B.id and A.Row1<b.row1) as [SecondNextBiggestValue],
(A.id -(SELECT MIN( id ) as ID FROM CTE B where A.Id<>B.id and A.Row1<b.row1)) as Difference

from CTE A```

Hope it Helps!!

• Edited by Wednesday, February 13, 2013 8:44 PM
•
• Wednesday, February 13, 2013 8:15 PM

Hi tsql_new

Cursors are deprecated meaning they will no longer be supported. Please try:

```declare @table table (column1 int, column2 int)
insert into @table (column1) values (340),(12),(10),(15)

if OBJECT_ID(N'tempdb..#temp',N'U') is not null drop table #temp
select column1 into #temp from @table

while exists(select 1 from #temp)
begin
declare @holdme int = (select top 1 column1 from #temp)
declare @max int = (select MAX(@holdme - column1) from @table)
update @table set column2 = @max where column1 = @holdme
delete top (1) from #temp
end

select * from @table```

Pérez

• Wednesday, February 13, 2013 8:26 PM
Moderator

Here is the query:

```create table test (col int)
insert into test values (340),(12),(10),(15)

Select col, col-Min(col) Over() as NewCol from test

drop table test
```

By the way, please don't think about Cursor for you normal queries. It is not suitable for most cases. But it is not deprecated, it can useful in some cases like administrative jobs, you may need to run row by row to get your job done.

• Proposed As Answer by Thursday, February 14, 2013 5:18 PM
• Marked As Answer by Thursday, February 21, 2013 9:39 AM
•
• Wednesday, February 13, 2013 8:33 PM

By the way, please don't think about Cursor for you normal queries. It is not suitable for most cases. But it is not deprecated, it can useful in some cases like administrative jobs, you may need to run row by row to get your job done.

Hi All

I do apologize, I believed to have read somewhere (msdn maybe?) but it appears that cursors are not being deprecated since it is not mentioned for SQL Server 2012. Unless someone can point to an article where it is stated? In either case as stated by Jingyang Li they should not be used for every occasion.

Pérez