On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,584 Users
  • 2,269,760 Discussions
  • 7,916,784 Comments

Discussions

Sorting the data set

2652054
2652054 Member Posts: 108 Green Ribbon
edited Jul 11, 2020 11:01AM in SQL & PL/SQL

Hi Experts,

I have the below data set for sales order and sequence in a table. This table is at vendor location and I access the data via dblink. I can only select these two columns. Now i need to  select in such a way that i select distinct so numbers but it should be in the sorted as per below table ,i.e. 7037561280,7037567671,7037633589,7037630443,7037627397,7037601762 ....7037619156. If i do a select distinct or group by so number , i get distinct values but they are not in the same order as below . I tried using RANK and DENSE_RANK function as below to check if i can use these to get the desired output. But it does not work because when i do a partition by so-number , the rank again begins with 1.

Hence can you help me/guide me to get the sorted so_number from below data set. I used below script

            SELECT so_number

                         ,seq_no,

                       DENSE_RANK() OVER (PARTITION BY so_number ORDER BY seq_no) AS myrank

          FROM    

                      xx_om_sellout_data        

          order by seq_no

so_numberseq_no
70375612802
70375612803
70375676714
70375676715
70375676716
70375676717
70376335898
70376335899
703763044310
703763044311
703762739712
703762739713
703760176214
703760176215
703759636416
703759636417
703761915618
703761915619

Regards,

Tagged:
2652054

Best Answer

Answers

  • BEDE
    BEDE Oracle Developer Bucharest, RomaniaMember Posts: 2,484 Gold Trophy
    edited Jul 10, 2020 7:44AM

    So, do you want to have unique so_number values and no duplicate so_number? And if you want unique so_number, would you desire the max(seq_no) or min(seq_no)?

    You may use group by:

    select

    so_number

    ,max(seq_no)

    ,min(seq_no)

    from

    FROM    xx_om_sellout_data

    group by so_number 

    order by so_number

    ;

    Or what?

    26520542652054
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Jul 10, 2020 7:44AM Answer ✓

    SELECT  SO_NUMBER

      FROM  XX_OM_SELLOUT_DATA

      GROUP BY SO_NUMBER

      ORDER BY MIN(SEQ_NO)

    /

    SY.

  • 2652054
    2652054 Member Posts: 108 Green Ribbon
    edited Jul 10, 2020 8:07AM

    Hi Solomon,

    Thanks for the response this works. However if possible request you kindly explain or share any link of how does order by min (column) / max(column) sorts the data set. I am asking this because my understanding was to use order by clause the column should be present in the selected list of columns.

    Regards,

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Jul 10, 2020 8:19AM

    GROUP BY returns one row per SO_NUMBER. MIN(SEQ) provides smallest SEQ_NO for each SO_NUMBER. ORDER BY sorts SO_NUMBERS according to their smallest SEQ_NO.

    SY.

    26520542652054
  • 2652054
    2652054 Member Posts: 108 Green Ribbon
    edited Jul 10, 2020 9:30AM

    thank you so much for your time and help !

  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Jul 10, 2020 9:47AM

    You can order by columns not on the SELECT, just do the following:

    WITH vData AS

    ( SELECT 1 AS Id, 'John' AS Name, 10 AS Priority FROM dual

      UNION ALL

      SELECT 2 AS Id, 'Peter' AS Name, 5 AS Priority FROM dual

      UNION ALL

      SELECT 3 AS Id, 'Jane' AS Name, 50 AS Priority FROM dual

      UNION ALL

      SELECT 4 AS Id, 'Mary' AS Name, 2 AS Priority FROM dual

      UNION ALL

      SELECT 5 AS Id, 'Adam' AS Name, 20 AS Priority FROM dual

      )

    SELECT Id, Name

    FROM vData

    ORDER BY Priority;

    Results:

    pastedImage_0.png

    The problem is that, although there is an ORDER in the data, a user cannot see it. This kind of behavior is useful when a process needs to access data in a particular order but does not uses that piece of data, but for humans it is always a good option to sort by something that you can actually SEE.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Jul 10, 2020 10:09AM
    L. Fernigrini wrote:The problem is that, although there is an ORDER in the data, a user cannot see it. This kind of behavior is useful when a process needs to access data in a particular order but does not uses that piece of data, but for humans it is always a good option to sort by something that you can actually SEE.

    OP needs DISTINCT values sorted, therefore ORDER BY can't contain other columns like PRIORITY in your example.

    SY.

    26520542652054
  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Jul 11, 2020 11:01AM

    Yes, I was answering to this comment in OP response to your answer:

     I am asking this because my understanding was to use order by clause the column should be present in the selected list of columns.

    I was not providing a complete solution to his problem (you had already done and OP had already selected it as correct). I was just letting OP know that you can order by something not on the SELECT clause.,

    2652054