Increment SQL column based on another

Question

Given that I have a query like this:

select employeeid as id, department as dep from employee where department in (...) 
order by department

I would like to have an extra column that increments for every 3 departments so that the result looks something like this:

id dep        magic
1  Accounting 0
30 Accounting 0
66 HR         0
67 HR         0
33 HR         0
2  Sales      0
9  IT         1
12 R&D        1
19 Design     1
45 QA         2
46 QA         2

I have tried with various row number functions, over() and other voodoo, but I cant seem to get it right. Hope you can help me with this one!

Edit: So this was not as straight forward as I had hoped. The example above probably didnt capture all the issues involved in my case. So this is the real query that I am running:

select    sdi.sdcid
         ,sdi.keyid1
         ,sdi.paramlistid
         ,sdi.paramlistversionid
         ,sdi.variantid
         ,sdi.dataset
         ,sdi.paramid
         ,sdi.paramtype
         ,sdi.replicateid
         ,sd.sdidataid
        --(ROW_NUMBER() over(order by sd.sdidataid asc) -1) /3 partition
         from sdidataitem sdi
         left join sdidata sd on 
         sdi.keyid1 = sd.keyid1 
         and sdi.keyid2 = sd.keyid2 
         and sdi.keyid3 = sd.keyid3 
         and sdi.paramlistid = sd.paramlistid 
         and sdi.paramlistversionid = sd.paramlistversionid 
         and sdi.variantid = sd.variantid 
         and sdi.dataset = sd.dataset 
         where sd.sdidataid in (select top 30 sdidataid from sdidata) 
         and sdi.u_analysserieid is null
         order by sd.sdidataid asc

when I run the query with the partition column I get the following error: Cannot find either column "sd" or the user-defined function or aggregate "sd.sdidataid", or the name is ambiguous. This is the code to create the two tables involved:

CREATE TABLE [lv2056d].[sdidata]  ( 
[sdcid]                     nvarchar(40) NOT NULL,
[keyid1]                    nvarchar(40) NOT NULL,
[keyid2]                    nvarchar(40) NOT NULL,
[keyid3]                    nvarchar(40) NOT NULL,
[paramlistid]               nvarchar(40) NOT NULL,
[paramlistversionid]        nvarchar(40) NOT NULL,
[variantid]                 nvarchar(40) NOT NULL,
[dataset]                   numeric(18,0) NOT NULL,
[limitruleid]               nvarchar(40) NULL,
[limitruleversionid]        nvarchar(40) NULL,
[modifiableflag]            nvarchar(1) NULL,
[approvalsequenceflag]      nvarchar(1) NULL,
[approvalpassrule]          nvarchar(20) NULL,
[condition]                 nvarchar(80) NULL,
[availabilityflag]          nvarchar(1) NULL,
[workflowid]                nvarchar(40) NULL,
[workflowversionid]         nvarchar(40) NULL,
[workflowinstance]          numeric(18,0) NULL,
[usersequence]              numeric(18,0) NULL,
[notes]                     nvarchar(2000) NULL,
[auditsequence]             numeric(18,0) NULL,
[auditdeferflag]            nvarchar(1) NULL,
[tracelogid]                nvarchar(40) NULL,
[createdt]                  datetime NULL,
[createby]                  nvarchar(40) NULL,
[createtool]                nvarchar(20) NULL,
[moddt]                     datetime NULL,
[modby]                     nvarchar(40) NULL,
[s_datasetstatus]           nvarchar(20) NULL,
[modtool]                   nvarchar(20) NULL,
[s_notebookreference]       nvarchar(20) NULL,
[s_assignedanalyst]         nvarchar(40) NULL,
[s_instrumentid]            nvarchar(40) NULL,
[s_icoverriddenflag]        nvarchar(1) NULL,
[s_retestedflag]            nvarchar(1) NULL,
[s_remeasuredflag]          nvarchar(1) NULL,
[s_remeasureinstance]       numeric(18,0) NULL,
[s_qcbatchid]               nvarchar(40) NULL,
[s_qcbatchitemid]           nvarchar(20) NULL,
[trackitemid]               nvarchar(40) NULL,
[scheduleplanid]            nvarchar(40) NULL,
[scheduleplanitemid]        nvarchar(40) NULL,
[uniquenessflag]            nvarchar(1) NULL,
[approvalflag]              nvarchar(1) NULL,
[sdidataid]                 nvarchar(40) NULL,
[sourceworkitemid]          nvarchar(40) NULL,
[sourceworkiteminstance]    numeric(18,0) NULL,
[s_cancellableflag]         nvarchar(1) NULL,
[documentid]                nvarchar(40) NULL,
[documentversionid]         nvarchar(40) NULL,
[blockflag]                 nvarchar(1) NULL,
[activeflag]                nvarchar(1) NULL,
[s_assigneddepartment]      nvarchar(40) NULL,
[s_instrumentusedflag]      nvarchar(1) NULL,
CONSTRAINT [xpksdidata] PRIMARY KEY CLUSTERED([sdcid],[keyid1],[keyid2],[keyid3],[paramlistid],[paramlistversionid],[variantid],[dataset]))
GO
CREATE TABLE [lv2056d].[sdidataitem]  ( 
[sdcid]                 nvarchar(40) NOT NULL,
[keyid1]                nvarchar(40) NOT NULL,
[keyid2]                nvarchar(40) NOT NULL,
[keyid3]                nvarchar(40) NOT NULL,
[paramlistid]           nvarchar(40) NOT NULL,
[paramlistversionid]    nvarchar(40) NOT NULL,
[variantid]             nvarchar(40) NOT NULL,
[dataset]               numeric(18,0) NOT NULL,
[paramid]               nvarchar(40) NOT NULL,
[paramtype]             nvarchar(80) NOT NULL,
[replicateid]           numeric(18,0) NOT NULL,
[aliasid]               nvarchar(80) NULL,
[mandatoryflag]         nvarchar(1) NULL,
[datatypes]             nvarchar(20) NULL,
[enteredvalue]          numeric(28,10) NULL,
[enteredtext]           nvarchar(255) NULL,
[enteredunits]          nvarchar(40) NULL,
[operatorrule]          nvarchar(255) NULL,
[transformvalue]        numeric(28,10) NULL,
[transformdt]           datetime NULL,
[transformtext]         nvarchar(255) NULL,
[transformrule]         nvarchar(4000) NULL,
[displayvalue]          nvarchar(255) NULL,
[displayunits]          nvarchar(40) NULL,
[displayformat]         nvarchar(255) NULL,
[rangeoperator]         nvarchar(20) NULL,
[enteredqualifier]      nvarchar(20) NULL,
[entrysdcid]            nvarchar(40) NULL,
[entryreftypeid]        nvarchar(40) NULL,
[calcrule]              nvarchar(4000) NULL,
[measurementactionid]   nvarchar(40) NULL,
[releasedflag]          nvarchar(1) NULL,
[valuestatus]           nvarchar(20) NULL,
[condition]             nvarchar(80) NULL,
[transformdeferflag]    nvarchar(1) NULL,
[textcolor]             numeric(18,0) NULL,
[usersequence]          numeric(18,0) NULL,
[notes]                 nvarchar(2000) NULL,
[auditsequence]         numeric(18,0) NULL,
[auditdeferflag]        nvarchar(1) NULL,
[tracelogid]            nvarchar(40) NULL,
[createdt]              datetime NULL,
[createby]              nvarchar(40) NULL,
[createtool]            nvarchar(20) NULL,
[moddt]                 datetime NULL,
[modby]                 nvarchar(40) NULL,
[modtool]               nvarchar(20) NULL,
[s_acoverriddenflag]    nvarchar(1) NULL,
[s_analystid]           nvarchar(40) NULL,
[s_qcevalstatus]        nvarchar(20) NULL,
[displayvalueformat]    nvarchar(255) NULL,
[calcexcludeflag]       nvarchar(1) NULL,
[sdidataitemid]         nvarchar(40) NULL,
[instrumentid]          nvarchar(40) NULL,
[instrumentfieldid]     nvarchar(20) NULL,
[activeflag]            nvarchar(1) NULL,
[externalreference]     nvarchar(255) NULL,
[resulttimeoffset]      numeric(28,10) NULL,
[u_analysserieid]       nvarchar(20) NULL,
[u_showindataentry]     nvarchar(20) NULL,
[w_instrumentid]        nvarchar(20) NULL,
[u_defvalset]           nvarchar(20) NULL,
CONSTRAINT [xpksdidataitem] PRIMARY KEY CLUSTERED([sdcid],[keyid1],[keyid2],[keyid3],[paramlistid],[paramlistversionid],[variantid],[dataset],[paramid],[paramtype],[replicateid]))
GO

this is a contrived example far from reality :) I will use it to fetch values from an array in my code (ugly) — oskbor
– oskbor, Commented Apr 2, 2013 at 13:52
ewwwww... But you gotta do what you gotta do, right? If you want a different look maybe on your (ugly) code, I'm up for that :) So if you provide the bigger picture (not all), we might put you on a different path? :-) — Steven Ryssaert
– Steven Ryssaert, Commented Apr 2, 2013 at 14:02

vittore · Accepted Answer · 2013-04-02 14:19:47Z

1

You can just use (ROW_NUMBER() OVER ...) / 3

select employeeid as id, department as dep , 
(row_number() over (order by department asc) -1) /3 partition
from employee 
where department in (...) 
order by department

About your query. 1) you seem to miss comma after first field 2) better use join if you really joining tables, just for clarity

select sd.sdidataid, (ROW_NUMBER() over(order by sd.sdidataid asc) -1) /3 partition 
from sdidataitem sdi inner join sdidata sd on 
    sdi.keyid1 = sd.keyid1 (... more column restrictions) 
and sdi.dataset = sd.dataset 
where sd.sdidataid in (select top 30 sdidataid from sdidata) 
and sdi.u_analysserieid is null 
order by sd.sdidataid asc

edited Apr 2, 2013 at 14:19

answered Apr 2, 2013 at 13:37

vittore

17.6k6 gold badges46 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

vittore Over a year ago

@danihp - He told he want value to be incremented for every 3 departments. I did exactly that. You will have value incremented for every three rows , not departments

vittore Over a year ago

@oskbor - so show you query, how could i know what is wrong with it.

oskbor Over a year ago

So this is the real query which fails: select sdidata.sdidataid (ROW_NUMBER() over(order by sdidata.sdidataid asc) -1) /3 partition from sdidataitem,sdidata where sdidataitem.keyid1 = sdidata.keyid1 (... more column restrictions) and sdidataitem.dataset = sdidata.dataset and sdidata.sdidataid in (select top 30 sdidataid from sdidata) and sdidataitem.u_analysserieid is null order by sdidata.sdidataid asc

oskbor Over a year ago

Cannot find either column "sdidata" or the user-defined function or aggregate "sdidata.sdidataid", or the name is ambiguous.

vittore Over a year ago

@oskbor does query work with row_number and without this sd.sdidataid in (select top 30 sdidataid from sdidata) condition ? If yes, add alias for subquery sd.sdidataid in (select top 30 sdidataid from sdidata sd2)

|

bummi · Accepted Answer · 2013-04-02 13:43:53Z

0

Declare @tab table (id int,dep varchar(50))
insert into @tab Values(1,'Accounting'),(2,'Accounting'),(3,'Accounting'),(4,'AAA'),(5,'BBB'),(6,'ccc'),(7,'ddd'),(8,'eee'),(9,'fff');


Select t.*,a.Magic
from @tab t
join
(
Select dep,(ROW_NUMBER() over (order by dep) -1)/3 as Magic
from @tab
group by dep
) a on a.dep=t.dep
order by t.dep

answered Apr 2, 2013 at 13:43

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Collectives™ on Stack Overflow

Increment SQL column based on another

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related