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.
| 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 +1Can 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_dateFrom vsc_plans_base aInner 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_makeWhere 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
How to INCREMENT a value in a SELECT