Sorting the data set

2652054

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_number	seq_no
7037561280	2
7037561280	3
7037567671	4
7037567671	5
7037567671	6
7037567671	7
7037633589	8
7037633589	9
7037630443	10
7037630443	11
7037627397	12
7037627397	13
7037601762	14
7037601762	15
7037596364	16
7037596364	17
7037619156	18
7037619156	19

Regards,

Solomon Yakobson

SELECT  SO_NUMBER

  FROM  XX_OM_SELLOUT_DATA

  GROUP BY SO_NUMBER

  ORDER BY MIN(SEQ_NO)

/

SY.

BEDE

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?

Solomon Yakobson

SELECT  SO_NUMBER

  FROM  XX_OM_SELLOUT_DATA

  GROUP BY SO_NUMBER

  ORDER BY MIN(SEQ_NO)

/

SY.

2652054

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

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.

2652054

thank you so much for your time and help !

L. Fernigrini

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:

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

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.

L. Fernigrini

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.,