Re: -- Sorting is nice ?!?

"Dave" <davidr212000_at_yahoo.com> wrote in message news:5e092a4e.0404091020.25d9d667_at_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

Andre,

did you realize that oracle was giving you the correct sort order on character columns? so this is not purely an oracle issue or even a database issue, but is a primarily a design issue

dave's response gives one algorithm for converting your chapter 'tags' to actual numeric values -- but be aware that it assumes no more than 100 sub-sections

your chapter tags are actually derived values (what happens to 10.2.4 when section 10.2.3 gets deleted?, what happens when chapter 2 gets moved to the end of the document?), so whatever code you use to set (derive) the tags should also derive the sort value

;-{ mcs Received on Sat Apr 10 2004 - 06:13:27 CDT