pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
case-insensitive SORT BY?
4+ messages / 4 participants
[nested] [flat]

* case-insensitive SORT BY?
@ 1998-09-22 19:53  Patrick Giagnocavo <pgiagnoc@qi.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Patrick Giagnocavo @ 1998-09-22 19:53 UTC (permalink / raw)
  To: pgsql-sql

I am trying to ensure that when I do a SORT BY I get back the results in
normal sorted order instead of case-sensitive order.  The WHERE clause
contains a LIKE '%..%' so I cannot use UPPER here in a way that does
what I want.

e.g. , given 

A, B, b, a 

as data, the normal SORT BY behavior returns

a
b
A
B

How do I make it return

a
A
b
B

instead?

./patrick



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] case-insensitive SORT BY?
@ 1998-09-22 21:41  Richard Lynch <lynch@lscorp.com>
  0 siblings, 1 reply; 4+ messages in thread

From: lynch@lscorp.com @ 1998-09-22 21:41 UTC (permalink / raw)
  To: Patrick Giagnocavo <pgiagnoc@qi.com>; pgsql-sql

Cheat.  Get the name back, *AND* a second "field" with the name in upper
case, and then sort by that upper-cased name.  EG:

"select name, upper(name) as key where name like '%clinton%' sort by key"

At 3:53 PM 9/22/98, Patrick Giagnocavo wrote:
>I am trying to ensure that when I do a SORT BY I get back the results in
>normal sorted order instead of case-sensitive order.  The WHERE clause
>contains a LIKE '%..%' so I cannot use UPPER here in a way that does
>what I want.
>
>e.g. , given
>
>A, B, b, a
>
>as data, the normal SORT BY behavior returns
>
>a
>b
>A
>B
>
>How do I make it return
>
>a
>A
>b
>B
>
>instead?

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] case-insensitive SORT BY?
@ 1998-09-23 12:47  Chris Johnson <cmj@inline-design.com>
  parent: Richard Lynch <lynch@lscorp.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Chris Johnson @ 1998-09-23 12:47 UTC (permalink / raw)
  To: Richard Lynch <lynch@lscorp.com>; +Cc: Patrick Giagnocavo <pgiagnoc@qi.com>; pgsql-sql

Perhaps this should go in the FAQ?  I asked it myself several months ago.

Also note that you probably want to sort by key followed by name ( ie ...
sort by key, name) so that you don't run the risk of getting something
like:

a
A
B
b

Chris
--
I am at one with my duality.

On Tue, 22 Sep 1998, Richard Lynch wrote:

> Cheat.  Get the name back, *AND* a second "field" with the name in upper
> case, and then sort by that upper-cased name.  EG:
> 
> "select name, upper(name) as key where name like '%clinton%' sort by key"
> 
> At 3:53 PM 9/22/98, Patrick Giagnocavo wrote:
> >I am trying to ensure that when I do a SORT BY I get back the results in
> >normal sorted order instead of case-sensitive order.  The WHERE clause
> >contains a LIKE '%..%' so I cannot use UPPER here in a way that does
> >what I want.
> >
> >e.g. , given
> >
> >A, B, b, a
> >
> >as data, the normal SORT BY behavior returns
> >
> >a
> >b
> >A
> >B
> >
> >How do I make it return
> >
> >a
> >A
> >b
> >B
> >
> >instead?
> 
> --
> --
> -- "TANSTAAFL" Rich lynch@lscorp.com
> 
> 
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] case-insensitive SORT BY?
@ 1998-09-23 18:31  David Hartwig <daveh@insightdist.com>
  parent: Chris Johnson <cmj@inline-design.com>
  0 siblings, 0 replies; 4+ messages in thread

From: David Hartwig @ 1998-09-23 18:31 UTC (permalink / raw)
  To: Chris Johnson <cmj@inline-design.com>; +Cc: Richard Lynch <lynch@lscorp.com>; Patrick Giagnocavo <pgiagnoc@qi.com>; pgsql-sql

Here is a 6.4  tidbit.

    select name where name like '%clinton%' order by upper(name), name;

is allowed.  (i.e.   functions are allowed in the ORDER clause)   Like you
said, the the second attribute in the ORDER clause will be the tie breaker.


Chris Johnson wrote:

> Perhaps this should go in the FAQ?  I asked it myself several months ago.
>
> Also note that you probably want to sort by key followed by name ( ie ...
> sort by key, name) so that you don't run the risk of getting something
> like:
>
> a
> A
> B
> b
>
> Chris
> --
> I am at one with my duality.
>
> On Tue, 22 Sep 1998, Richard Lynch wrote:
>
> > Cheat.  Get the name back, *AND* a second "field" with the name in upper
> > case, and then sort by that upper-cased name.  EG:
> >
> > "select name, upper(name) as key where name like '%clinton%' sort by key"
> >
> > At 3:53 PM 9/22/98, Patrick Giagnocavo wrote:
> > >I am trying to ensure that when I do a SORT BY I get back the results in
> > >normal sorted order instead of case-sensitive order.  The WHERE clause
> > >contains a LIKE '%..%' so I cannot use UPPER here in a way that does
> > >what I want.
> > >
> > >e.g. , given
> > >
> > >A, B, b, a
> > >
> > >as data, the normal SORT BY behavior returns
> > >
> > >a
> > >b
> > >A
> > >B
> > >
> > >How do I make it return
> > >
> > >a
> > >A
> > >b
> > >B
> > >
> > >instead?
> >
> > --
> > --
> > -- "TANSTAAFL" Rich lynch@lscorp.com
> >
> >
> >






^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1998-09-23 18:31 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-22 19:53 case-insensitive SORT BY? Patrick Giagnocavo <pgiagnoc@qi.com>
1998-09-22 21:41 Re: [SQL] case-insensitive SORT BY? Richard Lynch <lynch@lscorp.com>
1998-09-23 12:47 ` Chris Johnson <cmj@inline-design.com>
1998-09-23 18:31   ` David Hartwig <daveh@insightdist.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox