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 2008 Forums
 Transact-SQL (2008)
 How to INCREMENT a value in a SELECT

Author  Topic 

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-08-13 : 16:42:13
I have a select statement that I'm pulling data from, but I have a field i need to increment on each record that's returned by the SELECT.

Below in my code I tried achieving this by selecting the max value for that field then adding 1 to it, but that just puts the same value for all my returned records for that field.

I need the select statement to return a for vsc_plan_id increment base off 286006 +1

Can anyone assist me?

[CODE]
Select Distinct
(max(vsc_plan_id)+1) as vsc_plans_base_id
,CASE When b.participation_tier = 1 then a.vsc_plan_code
When b.participation_tier = 2 then a.vsc_plan_code||'T1'
END as vsc_plan_code
,a.vsc_plan_make
,a.vsc_plan_class
,a.vsc_plan_term
,a.vsc_plan_miles
, 'f' as vsc_plan_commercial
,a.vsc_plan_sku
,b.vsc_plan_dealer_price
, 0 as vsc_plan_internal_price
,Case When b.vsc_plan_odo_lower = 0 Then 1
Else b.vsc_plan_odo_lower
End as vsc_plan_odo_lower
,b.vsc_plan_odo_upper
,b.vsc_plan_deductible
,CASE When b.vsc_plan_deductible = 50 Then 0
ELSE 1
END as vsc_plan_disappearing_deductible
, a.vsc_plan_rgroup
, NULL as vsc_plan_retail_rate
, '2014-07-01' as vsc_plan_start_date
, '1799-12-31' as vsc_plan_end_date
, '2014-08-01' as vsc_plan_startb_date
, '1799-12-31' as vsc_plan_endb_date

From vsc_plans_base a
Inner Join vsc_plans_base_excel b
On a.vsc_plan_code = b.vsc_plan_code
and a.vsc_plan_class = b.vsc_plan_class
and a.vsc_plan_term = b.vsc_plan_term
and a.vsc_plan_miles = b.vsc_plan_miles
and a.vsc_plan_make = a.vsc_plan_make

Where a.vsc_plan_make = 'NA' Also
and a.vsc_plan_rgroup =5

[/CODE]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-13 : 16:46:17
Have you tried ROW_NUMBER()?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -