Results 1 to 6 of 6

Thread: [RESOLVED] Write a query to assign a sequential number to each row in a group

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] Write a query to assign a sequential number to each row in a group

    Right at the start I will confess I am being lazy asking for help writing a query because I think someone else would be able to do it very fast and I'd have to use a lot of brainpower myself, but if you want to tell me to go take a hike I will.

    In our database we have groups of records that belong to a customer. These records represent systems. So customer 100100 might have 5 systems. One of the columns is SystemNumber and it used for ordering. So when the systems are displayed in a datagrid they are ordered by SystemNumber.

    SystemNumber can be null and often it is. So the position in the grid for those records is random (but is probably in the order the rows are created newest to oldest).

    I want to go through the whole table and update the SystemNumber and then I'll make it NOT NULL.

    Oh, I didn't say why this is important. In the grid there are two buttons up/down which are supposed to transpose a record and its neighbor, either the one above or below, by updating the SystemNumber since that is how the rows are ordered in the grid. My code is crashing because I didn't write it to handle nulls.
    Code:
            /// <summary>
            /// 
            /// </summary>
            /// <param name="idxOne">the index of the row selected</param>
            /// <param name="idxTwo">the index of the row we're swapping SytemNumber with.  It's the row above if moving up or the row below if moving down</param>
            private void SwapOrderOfSystemDataNumber(int idxOne, int idxTwo)
            {
                DataRow row1 = gvSystemData.GetDataRow(idxOne);
                DataRow row2 = gvSystemData.GetDataRow(idxTwo);
                object val1 = row1[11];
                object val2 = row2[11];
                row1[OrderFieldName] = val2;
                row2[OrderFieldName] = val1;
    
                gvSystemData.FocusedRowHandle = idxTwo;
    
                using (SystemDataIIITableAdapters.tblSystemDataTableAdapter taTable = new SystemDataIIITableAdapters.tblSystemDataTableAdapter())
                {
                    using (SystemDataIII.tblSystemDataDataTable dtTable = new SystemDataIII.tblSystemDataDataTable())
                    {
                        SystemDataIII.tblSystemDataRow dr;
    
                        taTable.Fill(dtTable, (int)row1.ItemArray[0]);
                        dr = (SystemDataIII.tblSystemDataRow)dtTable.Rows[0];
                        dr.SystemNumber = (int)row1.ItemArray[11];
                        taTable.Update(dr);
    
                        taTable.Fill(dtTable, (int)row2.ItemArray[0]);
                        dr = (SystemDataIII.tblSystemDataRow)dtTable.Rows[0];
                        dr.SystemNumber = (int)row2.ItemArray[11];
                        taTable.Update(dr);
                    }
                }
            }
    I am getting an exception obviously when SystemNumber which is in position 11 is null and I try to cast it to an int.

    So the query needs get all the rows for customer 100100 and assign SystemNumber = 1, then 2, then 3 for each row. Then get all the rows for the next customer and do the same.

    Alternatively, I could do the update in my Swap routine, or even do the update in the code that loads the grid, so even if you never swapped, every time you loaded a customer's data I could run a SystemNumberAdjust() routine checking for nulls and getting rid of them. Of course then, I could never update the table schema. So I have a way to do this on my own, but it wouldn't be SQL, and I think perhaps SQL is the better way to go to know it's all set from that point on.

    So you can decide if you want to help me or not .
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Write a query to assign a sequential number to each row in a group

    What is the database? If it's SQL Server then you can use the ROW_NUMBER function to generate sequential values for a result set. Some other databases may have similar functions.

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Write a query to assign a sequential number to each row in a group

    Yes, sorry. As I was writing and clicking out to google if there was a solution to this posted somewhere, I remembered I had to note it's SQL Server, then forgot. Yes it's SQL Server. I will look up ROW_NUMBER.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Write a query to assign a sequential number to each row in a group

    I found this example exactly matching what I want to do. I will let you know how it goes for me.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Write a query to assign a sequential number to each row in a group

    Code:
    with x_tblSYstemData_MarleneIsTesting
    as
    (
    select *
    , ROW_NUMBER() OVER (PARTITION BY [OwnerID] ORDER BY OwnerID, SystemDataControl) AS Seq
    from tblSYstemData_MarleneIsTesting 
    WHERE OwnerID != '' AND SystemNumber is null
    )
    update x_tblSYstemData_MarleneIsTesting
    set SystemNumber = Seq
    go
    I'm going to test and do some further data-analysis and either mark solved or ask more. Thank you!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Write a query to assign a sequential number to each row in a group

    I tweaked this a tiny bit (there may have been something slightly wrong with my PARTION BY and ORDER BY the first time around) so here it is in case anyone needing help in the future finds this they have my final answer.

    Code:
    with x_tblSYstemData_MarleneIsTesting
    as
    (
    select *
    , ROW_NUMBER() OVER (PARTITION BY [OwnerID] ORDER BY SystemDataControl) AS Seq
    from tblSYstemData_MarleneIsTesting 
    WHERE OwnerID != '' AND  OwnerID != 'xxxdel' AND SystemNumber is null
    )
    update x_tblSYstemData_MarleneIsTesting
    set SystemNumber = Seq, LastModifiedOn = getdate(), LastModifiedBy = 'MMOCK'
    WHERE OwnerID != '' AND  OwnerID != 'xxxdel' AND SystemNumber is null
    go
    I wanted to update the table with getdate() and MMOCK so I could easily query for the rows I updated and examine them before I run this live.
    I need to skip some records, namely a blank ownerid or an ownerid we've "deleted" (equal to xxxdel). And if there's already a SystemNumber I'll leave those be.

    Thanks jmc - it was fun sort of writing it on my own! (P.S. Sorry I can't add to your reputation; I guess you're the only one who's been helping me lately ).
    Last edited by MMock; Mar 9th, 2018 at 11:09 AM. Reason: Added post script
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width