Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2011
    Posts
    20

    Question Unanswered: Custom Sort & Retain Uniqueness

    Hi,

    I'm looking for a solution for the following requirement. Any help is appreciated.

    Current table data:
    Code:
    item_id    seq
    -------    ---
    1001         1
    1002         1
    1003         2
    1004       3.1
    1005       3.2
    1006       3.2
    1007         4
    Need UPDATE / MERGE statement to update the records to have unique sequence numbers without any duplicates. The first sorting need to be done on the seq column followed by the item_id column.

    Expected output:
    Code:
    item_id    seq
    -------    ---
    1001         1
    1002         2
    1003         3
    1004       4.1
    1005       4.2
    1006       4.3
    1007         5

    Regards,
    Rohit

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why is expected output not like below
    Code:
    item_id    seq
    -------    ---
    1001         1
    1002         2
    1003         3
    1004         4
    1005         5
    1006         6
    1007         7
    Why are some SEQ values whole number & others not whole?
    Why not all SEQ have fractional value?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2011
    Posts
    20
    Hi,

    Basically all the values are decimal in nature, but ".0" is omitted saved as a whole value. The field used to store this data is a VARCHAR column.

    The sequence in this case represents a group and sub-group. Since some of the items have sub-groups, so the decimal values can start/end at a random item id.


    Regards,
    Rohit

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Bad example

    Quote Originally Posted by Rohit Oberoi View Post
    Hi,

    Basically all the values are decimal in nature, but ".0" is omitted saved as a whole value. The field used to store this data is a VARCHAR column.

    The sequence in this case represents a group and sub-group. Since some of the items have sub-groups, so the decimal values can start/end at a random item id.

    Regards,
    Rohit
    Your example does not reflect what you say, why not this result?:
    Code:
    	   old   new
    item_id    seq   seq
    -------    ---   ---
    1001         1   1.1
    1002         1   1.2
    1003         2   2.0
    1004       3.1   3.1
    1005       3.2   3.2
    1006       3.2   3.3
    1007         4   4.0
    If what you say is correct, then give us the rules of the transformation.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2011
    Posts
    20
    Hi,

    Pardon me for the confusion and multiple iteration on this. The details are as below:

    • There are list of parent items with item number: A1, B1, etc.

    • There are parent and child items for the parent items. In these scenarios, only the child items are stored in this table.

    • There are few items which are stored with incorrect sequence (no direct solution as that's how it's loaded.

    • Since the sequence is duplicate, one of the items are hidden from the list. This fix is to resolve the sequence and have it in sequential manner.

    • Neither the item number nor the item id are in sequential order. This is just an illustration.



    Code:
    	      old   new    item
    item_id    seq   seq    number
    -------    ---   ---     -------
    1001         1   1.0      A1
    1002         1   2.0      B1
    1003         2   3.0      C1
    1004       3.1   4.1     D12
    1005       3.2   4.2     D16
    1006       3.2   4.3     D11
    1007         4   5.0     E14
    Let me know if this makes sense.


    Regards,
    Rohit

  6. #6
    Join Date
    Nov 2011
    Posts
    20
    Here's the rules:
    • Sort on seq, item_id

    • There shouldn't be any duplicate seq. If there are, then bump to the next value for the whole list.

    • The values having valid decimal values, .1, .2, etc. should share the same number as it was grouped. For example, 3.1, 3.2, 3.3 series need to be updated to 4.1, 4.2, 4.3.

    • There can be multiple sets, like 3.1, 3.2, 3.3, again 4.1, 4.2, etc. So, after update, it should retain in the same group, like 4.1, 4.2, 4.3 and 5.1, 5.2, etc.


    FYI, I tried using the rownum, and applying a math formula that calculates the difference between rownum and the seq, but doesn't seem to work out. This may not be the right way. Also, attempting to do using Oracle "level", but no success yet.


    Regards,
    Rohit

  7. #7
    Join Date
    Nov 2011
    Posts
    20
    Here's the rules:

    • Sort by seq & item_id

    • There shouldn't be any duplicate seq, like 1, 2. If found then bump the list to the next sequence.

    • If any of valid decimal value is found, like .1, .2, etc. then those need to be under same group (whole number). like 3.1, 3.2, 3.3 and 4.1, 4.2 to 4.1, 4.2, 4.3 and 5.1, 5.2

    • There sub-group can be at any level, and the sub-group numbering / sequencing need to be retained.


    FYI, i attempted solving this using rownum and adding a math to determine the delta between the rownum and the sequence, but didn't work out. Also, trying to solve using Oracle "level", but no success yet.


    Regards,
    Rohit

  8. #8
    Join Date
    Nov 2011
    Posts
    20
    This case is probably a small alteration to the below post, where an additional taks of eliminating the duplicates need to be taken care and have to update same field using MERGE statement.

    Ask Tom "sorting by number" - Oracle


    Regards,
    Rohit

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Could the values having valid decimal values co-exist with a whole number value?

    For example:
    Code:
    item_id    seq
    -------    ---
    1011         7
    1012       7.1
    1013       7.3
    1014       7.3
    1015       7.4
    1016       7.4

    (2) If the number of items in a sub-group was more than 10,
    how to number them?

    For example:
    Code:
    item_id    seq
    -------    ---
    1021       8.1
    1022       8.2
    1024       8.3
    1025       8.4
    1026       8.4
    1027       8.4
    1028       8.4
    1029       8.4
    1031       8.5
    1033       8.6
    1034       8.7
    1035       8.8
    1037       8.9

    (3) Would you provide a concrete example of
    •There sub-group can be at any level, and the sub-group numbering / sequencing need to be retained.

  10. #10
    Join Date
    Nov 2011
    Posts
    20
    Hi Tonkuma,

    Thank you for looking into this. Here's the details:

    Quote Originally Posted by tonkuma View Post
    (1) Could the values having valid decimal values co-exist with a whole number value?

    For example:
    Code:
    item_id    seq
    -------    ---
    1011         7
    1012       7.1
    1013       7.3
    1014       7.3
    1015       7.4
    1016       7.4
    The answer is no, when valid decimal values present, it won't contain whole number (or decimal with .0 suffix).

    Quote Originally Posted by tonkuma View Post
    (2) If the number of items in a sub-group was more than 10,
    how to number them?

    For example:
    Code:
    item_id    seq
    -------    ---
    1021       8.1
    1022       8.2
    1024       8.3
    1025       8.4
    1026       8.4
    1027       8.4
    1028       8.4
    1029       8.4
    1031       8.5
    1033       8.6
    1034       8.7
    1035       8.8
    1037       8.9
    In this case, it will follow the natural order, like below. It shouldn't mix in between like 8.1, 8.10, 8.11, 8.2.
    Code:
    item_id    seq
    -------    ---
    1021       8.1
    1022       8.2
    1024       8.3
    1025       8.4
    1026       8.4
    1027       8.4
    1028       8.4
    1029       8.4
    1031       8.5
    1033       8.6
    1034       8.7
    1035       8.8
    1037       8.9
    1038       8.10
    1039       8.11
    Quote Originally Posted by tonkuma View Post
    (3) Would you provide a concrete example of
    • There sub-group can be at any level, and the sub-group numbering / sequencing need to be retained.
    I mean to say there can be a combination of parent items and item sub-groups. Post applying the update, the grouping shouldn't be disturbed. ithe For example:
    Code:
    item_id    seq
    -------    ---
    1042       1
    1043       2.1
    1044       2.1
    1045       3
    1046       4.1
    1047       4.2
    1048       4.3
    1049       5
    In this case, the group of 2's should still contain 2 items and 4's should contain 3 sub-items. The id # 1044's sequence shouldn't be 4.4 (say), then it'll be a misrepresentation.

    Another example of sequence is like below.
    Code:
    item_id    seq
    -------    ---
    1042       1.1
    1043       1.2
    1044       1.3
    1045       2
    1046       3
    1047       4
    1048       5.1
    1049       5.1

    Regards,
    Rohit

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Rohit Oberoi View Post

    The answer is no, when valid decimal values present, it won't contain whole number (or decimal with .0 suffix).
    Good.

    In this case, it will follow the natural order, like below. It shouldn't mix in between like 8.1, 8.10, 8.11, 8.2.
    Code:
    item_id    seq
    -------    ---
    1021       8.1
    1022       8.2
    1024       8.3
    1025       8.4
    1026       8.4
    1027       8.4
    1028       8.4
    1029       8.4
    1031       8.5
    1033       8.6
    1034       8.7
    1035       8.8
    1037       8.9
    1038       8.10
    1039       8.11
    If you want to distinguish 8.1 and 8.10,
    you shouldn't use the words like "decimal" (I think).
    Because, decimal number 1.1 and 1.10 must be equivalent.

    If you want to distinguish 1.1 and 1.10,
    I want to see the descriptions like "two digits separated by a dot or one digits".
    (Actually, you wrote "The field used to store this data is a VARCHAR column")

    Anyway,
    your descripions(which were using the word/concept of decimal number) were cofused me.


    Here is my idea which was worked on DB2.
    (some amendments must be necessary to work on Oracle.)

    Test data:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM Rohit.Uniqueness;
    ------------------------------------------------------------------------------
    
    ITEM_ID     SEQ    
    ----------- -------
           1001     1.0
           1002     1.0
           1003     2.0
           1004     3.1
           1005     3.2
           1006     3.2
           1007     4.0
           1012     7.1
           1013     7.3
           1014     7.3
           1015     7.4
           1016     7.4
           1021     8.1
           1022     8.2
           1024     8.3
           1025     8.4
           1026     8.4
           1027     8.4
           1028     8.4
           1029     8.4
           1031     8.5
           1033     8.6
           1034     8.7
           1035     8.8
           1037     8.9
    
      25 record(s) selected.
    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT item_id , seq
         , VARCHAR(
              COUNT(CASE
                    WHEN TRUNC(seq) = seq
                     OR  rn         = 1   THEN
                         0
                    END )
                 OVER(ORDER BY seq , item_id)
           )
           || CASE
              WHEN TRUNC(seq) <> seq THEN
                   '.' || VARCHAR(rn)
              ELSE ''
              END
           AS new_seq
     FROM  (SELECT t.*
                 , ROW_NUMBER()
                      OVER(PARTITION BY TRUNC(seq)
                               ORDER BY seq , item_id) AS rn
             FROM  Rohit.Uniqueness AS t
           )
    ;
    Results:
    Code:
    ------------------------------------------------------------------------------
    
    ITEM_ID     SEQ     NEW_SEQ                         
    ----------- ------- --------------------------------
           1001     1.0 1                               
           1002     1.0 2                               
           1003     2.0 3                               
           1004     3.1 4.1                             
           1005     3.2 4.2                             
           1006     3.2 4.3                             
           1007     4.0 5                               
           1012     7.1 6.1                             
           1013     7.3 6.2                             
           1014     7.3 6.3                             
           1015     7.4 6.4                             
           1016     7.4 6.5                             
           1021     8.1 7.1                             
           1022     8.2 7.2                             
           1024     8.3 7.3                             
           1025     8.4 7.4                             
           1026     8.4 7.5                             
           1027     8.4 7.6                             
           1028     8.4 7.7                             
           1029     8.4 7.8                             
           1031     8.5 7.9                             
           1033     8.6 7.10                            
           1034     8.7 7.11                            
           1035     8.8 7.12                            
           1037     8.9 7.13                            
    
      25 record(s) selected.

  12. #12
    Join Date
    Nov 2011
    Posts
    20
    Hi,

    Thank you for the updates. I made it working in Oracle with few syntax changes, but noticed that it went wrong because of my earlier note. Both the whole number and the number with fractional units are required. The whole number represents the parent item and the fractions represent the sub-items underneath.

    Could you please help to update this?

    Code:
    ITEM_ID    SEQ   NEW_SEQ                         
    -------- ------ ---------
    1001        1.0         1                               
    1002        1.0         2                               
    1003        2.0         3                               
    1004        2.1       3.1                             
    1005        2.2       3.2                             
    1006        2.2       3.3                             
    1007        3.0         4                               
    1008        3.1       4.1

    Quote Originally Posted by tonkuma View Post
    If you want to distinguish 8.1 and 8.10,
    you shouldn't use the words like "decimal" (I think).
    Because, decimal number 1.1 and 1.10 must be equivalent.

    If you want to distinguish 1.1 and 1.10,
    I want to see the descriptions like "two digits separated by a dot or one digits".
    (Actually, you wrote "The field used to store this data is a VARCHAR column")

    Anyway,
    your descripions(which were using the word/concept of decimal number) were cofused me.
    Yes, you interpreted this correctly for the numbers stored in a VARCHAR column. There's a maximum of single dot separating two digits.


    Regards,
    Rohit

  13. #13
    Join Date
    Nov 2011
    Posts
    20
    After applying few tweaks, I made the sorting resolved. Thank you Tonkuma.

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
  •