Re: -- Sorting is nice ?!?

From: Dave <davidr212000_at_yahoo.com>
Date: 9 Apr 2004 11:20:33 -0700
Message-ID: <5e092a4e.0404091020.25d9d667@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1081517304.968461_at_yasure>...
> Andre wrote:
> > Hello all,
> >
> > In a database I have a char field in which i store my chapter numbers (for a book)
> >
> > So the fiels are like this:
> > 1.1
> > 1.1.1
> > 1.2
> > 1.6
> > 1.10
> > 2.2
> >
> > etc...
> > But When i sort these Chapters I get:
> > 1.1
> > 1.1.1
> > 1.10
> > 1.2
> > 1.6
> > 2.2
> >
> >
> > So chatper 1.10 is before chapter 1.2?
> >
> >
> > How can i solve this?
> >
> > regards and thanks,
> >
> > Anneke
> > The Netherlands
>
> The easiest way is to add an additional column to your table.
>
> If you were writing an application I'd suggest writing a function
> that returns the correct numbering and then sort by FUNCTION(column).

CREATE OR REPLACE FUNCTION RANK_CHAPTER(P_CHAPTER IN VARCHAR2) RETURN NUMBER
IS

 L_CHAPTER VARCHAR2(50);
 L_RANK    NUMBER := 0;
 L_POWER   NUMBER := 0;

BEGIN
  L_CHAPTER := P_CHAPTER||'.';
  WHILE INSTR(L_CHAPTER, '.') > 0
  LOOP

      L_RANK := L_RANK + TO_NUMBER(SUBSTR(L_CHAPTER, 1,
INSTR(L_CHAPTER, '.')-1))/POWER(1000, L_POWER);
      L_POWER := L_POWER + 1;
      L_CHAPTER := SUBSTR(L_CHAPTER, INSTR(L_CHAPTER, '.')+1);

  END LOOP;   RETURN L_RANK;
END;
/

CREATE TABLE BOOK
(
 CHAPTER VARCHAR2(50)
)

BEGIN

INSERT INTO BOOK VALUES ( '1.1'   );
INSERT INTO BOOK VALUES ( '1.1.1' );
INSERT INTO BOOK VALUES ( '1.2'   );
INSERT INTO BOOK VALUES ( '1.6'   );
INSERT INTO BOOK VALUES ( '1.10'  );
INSERT INTO BOOK VALUES ( '2.2'   );
INSERT INTO BOOK VALUES ( '2.1'   );
INSERT INTO BOOK VALUES ( '2.10.1' );
INSERT INTO BOOK VALUES ( '2.11.2'   );
INSERT INTO BOOK VALUES ( '2.20.1'   );
INSERT INTO BOOK VALUES ( '1.30.10'  );
INSERT INTO BOOK VALUES ( '1.30.11'   );

COMMIT;
END; select chapter, rank_chapter(chapter)
from book
order by rank_chapter(chapter);

CHAPTER RANK_CHAPTER(CHAPTER)

1.1     1.001 
1.1.1   1.001001 
1.2     1.002 
1.6     1.006 
1.10    1.01 
1.30.10 1.03001 
1.30.11 1.030011 
2.2     2.002 
2.10.1  2.010001 
2.11.2  2.011002 
2.20.1  2.020001 

Dave Received on Fri Apr 09 2004 - 13:20:33 CDT