logic

All Replies

• Wednesday, February 13, 2013 8:07 PM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Wednesday, February 13, 2013 8:08 PM

   

   
  

  0
  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 Stan210 Wednesday, February 13, 2013 8:44 PM
  •  
  • Reply
  • Quote

   
• Wednesday, February 13, 2013 8:15 PM

   

   
  

  0

  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

  • Reply
  • Quote

   
• Wednesday, February 13, 2013 8:26 PM

  Moderator

   

   
  

  1

  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 Peréz Thursday, February 14, 2013 5:18 PM
  • Marked As Answer by Iric WenModerator Thursday, February 21, 2013 9:39 AM
  •  
  • Reply
  • Quote

   
• Wednesday, February 13, 2013 8:33 PM

   

   
  

  0

  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

  • Reply
  • Quote