incement by 5 starting at 5

• cbrammer1219

  SSCarpal Tunnel

  Points: 4476

  More actions

  June 4, 2015 at 9:23 am

  #298729

  Hi I am trying to increment a column that I am creating in a select by 5's, example 5,10,20,25....first row starting at 5,

  Something like

  WITH CTE as

  (

  SELECT cast(5 as int) as myColumn from table

  )

  SELECT cte.myColumn + 5 from CTE

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 4, 2015 at 9:26 am

  #1803051

  I think row_number is they way to go here; then you cna just multiply it by five.

  WITH CTE as

  (

  SELECT row_number() over(order by MyPrimarykey) * 5 as myColumn from table

  )

  SELECT myColumn from CTE

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 4, 2015 at 11:25 am

  #1803096

  It might not relate to the question directly, but if the number will be stored, this could be an alternative.

  CREATE TABLE #Increments(

  id5 int IDENTITY(5,5),

  somevalue varchar(128)

  )

  INSERT INTO #Increments(somevalue)

  SELECT TOP 100 name

  FROM sys.all_objects

  SELECT *

  FROM #Increments

  ORDER BY id5

  DROP TABLE #Increments

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  June 4, 2015 at 6:17 pm

  #1803211

  I have a get numbers function that I use for exactly this sort of thing. It comes in handy often...

  IF OBJECT_ID('dbo.GetNumsAB','IF') IS NOT NULL

  DROP FUNCTION dbo.GetNumsAB;

  GO

  CREATE FUNCTION dbo.GetNumsAB(@low int, @high int, @gap int)

  RETURNS TABLE WITH SCHEMABINDING AS RETURN

  WITH

  L1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)), --10

  L3(N) AS (SELECT 1 FROM L1 a, L1 b, L1 c), --1000

  iTally AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM L3 a, L3 b) --1M

  SELECT TOP (ABS((@high-@low)/ISNULL(NULLIF(@gap,0),1)+1)) -- prevent divide by 0 error is @gap=0

  rn = N,

  n1 = ((N-1)*@gap+@low)

  FROM iTally

  WHERE @high >= @low; -- ensure that @high >= @low

  GO

  SELECT

  rn, -- ROW_NUMBER() is built in if you need it...

  n1

  FROM dbo.GetNumsAB(5,100,5);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001