sql server - SQL - Identity Group By -


in sql server 2012 database, i'm creating "tasks" table have compound primary key composed of 3 fields: 

issue_id [int] not null, issue_sub_id [int] not null, task_id [int] not null

issue_id , issue_sub_id foreign keys other tables. in table i'm creating, there can many tasks associated each issue_id / issue_sub_id combination.

what establish default value task_id column, similar if used identity(1,1), auto-increment based on issue_id / issue_sub_id group. example, task_id results follows, given provided issue_id / issue_sub_id values:

issue_id     issue_sub_id     task_id ========     ============     ======= 12345             1              1 12345             1              2 12345             1              3 12345             2              1 12345             2              2 67890             2              1 67890             2              2 67890             2              3

i'm familiar row_number() over(partition issue_id, issue_sub_id order issue_id, issue_sub_id) possible solution but, i'd column part of compound primary key of table, don't think work.

thanks in advance.

i agree sean - add identity column, , use computed column task id. though i've answered question 1 here, i'm not sure marking 1 duplicate. reason want use task_id part of primary key.
however, i'm not sure that's possible, since in order include computed column in primary key must persisted, , reason (i think it's because of use of udf) sql server not allow me mark persisted.
anyway, here proposed solution this:

first, create function calculate task id:

create function dbo.generatetaskid (     @row_id int,     @issue_id int,     @issue_sub_id int ) returns int begin      return      (         select count(*)         dbo.tasks         issue_id = @issue_id         , issue_sub_id = @issue_sub_id         , row_id <= @row_id     ) end go

then, create table task id computed column:

create table dbo.tasks (     row_id [int] identity(1,1),     issue_id [int] not null,     issue_sub_id [int] not null,     task_id dbo.generatetaskid(row_id, issue_id, issue_sub_id),      constraint pk_tasks primary key (row_id) ) go

now, test it:

insert tasks values (12345, 1), (12345, 1), (12345, 1), (12345, 2), (12345, 2), (67890, 2), (67890, 2), (67890, 2)  select * tasks

results:

row_id  issue_id    issue_sub_id    task_id 1       12345       1               1 2       12345       1               2 3       12345       1               3 4       12345       2               1 5       12345       2               2 6       67890       2               1 7       67890       2               2 8       67890       2               3

you can see live demo on rextester.


Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more