Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2016
    Posts
    5

    Answered: ROW_NUMBER() - Not sure why I'm struggling with this so much

    I have the simple table below and I want to add a new col ID which splits on Product ID and Colour, so it counts up when a new colour.

    Product ID Colour new col ID
    AB123 BLUE 1
    AB123 BLUE 2
    AB123 GREEN 1
    AB123 no colour 1

    DE546 BLUE 1
    DE546 GREEN 1
    DE546 no colour 1
    DE546 no colour 2

    XT786 BLUE 1
    XT786 BLUE 2
    XT786 BLUE 3
    XT786 RED 1
    XT786 RED 2
    XT786 no colour 1

    I'm trying various ways but not getting the result and at the moment not sure what I'm doing wrong.

    ,ROW_NUMBER() OVER(PARTITION BY [Colour] ORDER BY [Colour]) AS [new col ID]
    ,ROW_NUMBER() OVER(PARTITION BY [Colour] ORDER BY [Product ID],[Colour]) AS [new col ID]
    ,DENSE_RANK() OVER (ORDER BY [Product ID]) AS [new col ID]
    ,DENSE_RANK() OVER(PARTITION BY [Product ID] ORDER BY [Product ID],[Colour]) AS [new col ID]
    ,DENSE_RANK() OVER(PARTITION BY [Product ID] ORDER BY [Colour Distribution] DESC) AS [new col ID]

    If someone could please point me in the right direction that would be grand, in the meantime if I work it out will post up answer.

    Thx,
    Rog.

  2. Best Answer
    Posted by clawlan

    "
    Quote Originally Posted by rogerclerkwell2 View Post
    ,ROW_NUMBER() OVER(PARTITION BY [Product ID],[Colour] ORDER BY [Product ID],[Colour]) AS [new col ID]

    Yea, window function are a little tricky at first until you get the hang of them. I always think of PARTITION BY as the indication when you start the ROW_NUMBER over again. So according to your question, you want the ROW_NUMBER to reset at every change in ProductID and Color, so both of those go into the PARTITION BY clause. Then the ORDER BY "lines up" the results is the order you choose before it starts numbering them. So in this case, "line them up" by ProductID (ascending), then Colour (ascending), and number them in that order."


  3. #2
    Join Date
    Oct 2016
    Posts
    5

    Talking Think I've just worked it out

    ,ROW_NUMBER() OVER(PARTITION BY [Product ID],[Colour] ORDER BY [Product ID],[Colour]) AS [new col ID]

  4. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Read Dr. Codd

    I have the simple table below and I want to add a new col ID which splits on Product ID and Colour, so it counts up when a new colour.
    Actually you did not post a table at all! By definition, a table has to have a key, and we have no DDL. Your "new column id" is wrong on a couple of points. Putting spaces in a data element name makes all kinds of problems with other ISO standards, so we do not do that. More importantly, we do not mix metadata and data in the same table; you cannot have a column that refers to columns, so this name is just plain wrong.

    In the future please follow basic netiquette. Is this what you meant?

    CREATE TABLE Products
    (product_id CHAR(5) NOT NULL,
    color_name VARCHAR(10) DEFAULT 'no color' NOT NULL,
    degree_of_dup INTEGER DEFAULT 1 NOT NULL
    CHECK(degree_of_dup > 0),
    PRIMARY KEY (product_id, degree_of_dup > 0)
    );

    The "degree of duplication" comes directly from Dr. Codd. This is how he handles this sort of design problem without redundancy, jumping up to the metadata level and counting the physical rows (well, actually the physical records in the order they written into the list; not a relational view of the world at all).

    INSERT INTO Products
    ('AB123', 'BLUE', 2),
    ('AB123', 'GREEN', 1)
    ('AB123', 'no colour', 1),

    ('DE546', 'BLUE', 1),
    ('DE546', 'GREEN', 1),
    ('DE546', 'no colour', 2),

    ('XT786' ,'BLUE', 3),
    ('XT786', 'RED', 2),
    ('XT786', 'no colour', 1);

    When you are doing is still thinking with punch cards! Move up to a level of abstraction when dealing with "commodity items" now your function just met messes with the degree of duplication, incrementing or decrementing it and making sure that it never gets below one.

  5. #4
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by rogerclerkwell2 View Post
    ,ROW_NUMBER() OVER(PARTITION BY [Product ID],[Colour] ORDER BY [Product ID],[Colour]) AS [new col ID]

    Yea, window function are a little tricky at first until you get the hang of them. I always think of PARTITION BY as the indication when you start the ROW_NUMBER over again. So according to your question, you want the ROW_NUMBER to reset at every change in ProductID and Color, so both of those go into the PARTITION BY clause. Then the ORDER BY "lines up" the results is the order you choose before it starts numbering them. So in this case, "line them up" by ProductID (ascending), then Colour (ascending), and number them in that order.

Tags for this Thread

Posting Permissions

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