Home » SQL & PL/SQL » SQL & PL/SQL » Sorting logic (9.2.0.6)
Sorting logic [message #433574] Thu, 03 December 2009 01:41 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi All,
I am struggling to find a sort logic for this query. Can anyone help?

Create table test_sort(string_id number, compressor_id number);
insert into test_sort values(200, 1);
insert into test_sort values(200, 2);
insert into test_sort values(200, 3);
insert into test_sort values(200, 3);
insert into test_sort values(200, 2);
insert into test_sort values(200, 1);


I want the output to be sorted on compressor like this:

string_id      compressor_id
200              1
200              2
200              3
200              1
200              2
200              3


Please note that compressor id and string id are not any constant values and it can be any numbers. Basically the query has to sort on compressor id the way i have shown in the output.

Thanks
SA
Re: Sorting logic [message #433577 is a reply to message #433574] Thu, 03 December 2009 01:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
use row_number() over (partition by string_id, compressor_id order by string_id) and use that as your first sort column:
  1  select string_id
  2  ,      compressor_id
  3  from   (select row_number() over (partition by string_id, compressor_id order by string_id) as rn
  4          ,      string_id
  5          ,      compressor_id
  6          from   test_sort
  7         )
  8  order  by rn
  9  ,      string_id
 10* ,      compressor_id
SQL> /

 STRING_ID COMPRESSOR_ID
---------- -------------
       200             1
       200             2
       200             3
       200             1
       200             2
       200             3

6 rows selected.

[Edit: Added order by in row_number() plus an example]

[Updated on: Thu, 03 December 2009 02:04]

Report message to a moderator

Re: Sorting logic [message #433583 is a reply to message #433577] Thu, 03 December 2009 02:27 Go to previous message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thank you Frank for helping me so fast.

Regards,
SA
Previous Topic: insert into wrong column sometimes
Next Topic: How to retrieve values from PL/SQL Table Type in Procedure
Goto Forum:
  


Current Time: Thu Sep 29 15:59:37 CDT 2016

Total time taken to generate the page: 0.08391 seconds