oskbor
Reputation: 1592
Increment SQL column based on another
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
Upvotes: 2
Views: 468
Answers (2)
vittore
Reputation: 17579
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
Upvotes: 1
bummi
Reputation: 27377
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
Upvotes: 0
Related Questions
- SQL Increment based on another column
- Increment column value based on condition
- SQL increment a value based on different column
- SQL Increment value depending on changes in other columns
- Increment and Change counter based on change in a column value
- How to increment new column value conditionally
- Increment value based on another column
- Increment a counter based on change in value of another column using SQL
- Increment a value depending on a condition
- TSQL : Incrementing a column value based on another column's value (SQL Server)