Re: group by sorting

From: Rod Corderey <RodCorderey_at_Lane-Associates.com>
Date: Thu, 19 Oct 2000 08:17:27 +0100
Message-Id: <10654.119742@fatcity.com>

Yes strings are a problem.

If the "sorting" of the result is acceptable at a coarse level, for instance in some situations when ordering by a name for a user interface, it is adequate to coarse sort on just the first n characters, as human recognition takes care of the rest.

So if the key is say "Surname" and we can work with just first three characters of the string, then

group by to_number(ltrim(to_char(ascii(substr(surname,1,1)),'009'))

                    ||ltrim(to_char(ascii(substr(surname,2,1)),'009'))
			||ltrim(to_char(ascii(substr(surname,3,1)),'009'))
                   ) * -1

giving

  1 select surname
  2 from contacts
  3 where surname is not null
  4 and rownum < 6
  5 group by to_number(ltrim(to_char(ascii(substr(surname,1,1)),'009'))

  6                      ||ltrim(to_char(ascii(substr(surname,2,1)),'009'))
  7     ||ltrim(to_char(ascii(substr(surname,3,1)),'009'))
  8                     ) * -1,
  9*     surname

SQL> / SURNAME

Wallace
SINT
REES
NEEDHAM
ARCHER better of course would be a function so that the actual quality of sorting can be parameterised and consideration of sort orders taken into account.

You also have to bear in mind the "Kyte caveat" mentioned by Larry Elkins when considering the importance that ordering has within the application.

Rod

-- 
Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com


"Eric D. Pierce" wrote:


> 

> 

> Cool!

> 

> How do you get the inverse of other non-numeric fields?

> 

> thanks,

> ep

> 

> On 18 Oct 2000, at 6:20, Rod Corderey wrote:

> 

> Date sent:              Wed, 18 Oct 2000 06:20:56 -0800

> To:                     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> Send reply to:          ORACLE-L_at_fatcity.com

> From:                   Rod Corderey <RodCorderey_at_lane-associates.com>

> Subject:                Re: group by sorting

> 

> > As others have said , you can simply add an order by after the group by.

> >

> > Unless the group by is part of an inner query or inline view. In that case an order

> > by is not allowed.

> >

> > The way to overcome this is to group by the necessary columns to satisfy the group

> > functions but then add an overriding value to your group by to which you can apply

> > a factor of -1,

> >

> > eg

> > presuming that invoice_no is numeric

> >

> > then group by invoice_no, invoice_date

> >

> > becomes group by invoice_no*-1, to_number(to_char(invoice_date,'J')) * -1

> >

> > or something similar.

Received on Thu Oct 19 2000 - 02:17:27 CDT