Postgres Pro
Downloads
Home   >   mailing lists

Thread: sort / limit / range problem

sort / limit / range problem

From
Gary Stainburn
Date:
Hi folks,

I've got a glossary table that I'm trying to render to HTML.  However, I've 
got a problem when using order by and limit.  Below is an example of  a psql 
session showing my problem. Anyone got any reasons why the last select misses  
'Driver'?

nymr=# select glterm from glossary where glterm like 'D%' order by glterm;    glterm
----------------DampersDartDetonatorsDisposalDomeDraw BarDriverDriving WheelsDuty Fitter
(9 rows)

nymr=# select glterm from glossary where glterm > 'Driving Wheels' limit 1;  glterm
-------------Duty Fitter
(1 row)

nymr=# select glterm from glossary where glterm > 'Draw Bar' limit 1;    glterm
----------------Driving Wheels
(1 row)

nymr=# \d glossary                                   Table "glossary"Attribute |         Type          |
     Modifier
 
-----------+-----------------------+-----------------------------------------------------glid      | integer
  | not null default 
 
nextval('glossary_glid_seq'::text)glterm    | character varying(30) | not nullgldesc    | text                  |
Indices: glossary_pkey,        glossary_term_index

nymr=#

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000

Re: sort / limit / range problem

From
"Tomasz Myrta"
Date:
> Hi folks,
> 
> I've got a glossary table that I'm trying to render to HTML. 
>  However, I've got a problem when using order by and limit.  Below 
> is an example of  a psql session showing my problem. Anyone got any 
> reasons why the last select misses  'Driver'?
> 

Everything is ok. Without order by, limit has undeterminated result. It is 
described in Postgresql documentation.

Try this:

select glterm from glossary where glterm > 'Driving Wheels' 
order by glterm limit 1

select glterm from glossary where glterm > 'Draw Bar'
order by glterm limit 1

Regards,
Tomasz Myrta

Re: sort / limit / range problem

From
Achilleus Mantzios
Date:
On Wed, 5 Mar 2003, Gary Stainburn wrote:

> Hi folks,
> 
> I've got a glossary table that I'm trying to render to HTML.  However, I've 
> got a problem when using order by and limit.  Below is an example of  a psql 
> session showing my problem. Anyone got any reasons why the last select misses  
> 'Driver'?

You forgot ORDER BY glterm

> 
> nymr=# select glterm from glossary where glterm like 'D%' order by glterm;
>      glterm
> ----------------
>  Dampers
>  Dart
>  Detonators
>  Disposal
>  Dome
>  Draw Bar
>  Driver
>  Driving Wheels
>  Duty Fitter
> (9 rows)
> 
> nymr=# select glterm from glossary where glterm > 'Driving Wheels' limit 1;
>    glterm
> -------------
>  Duty Fitter
> (1 row)
> 
> nymr=# select glterm from glossary where glterm > 'Draw Bar' limit 1;
>      glterm
> ----------------
>  Driving Wheels
> (1 row)
> 
> nymr=# \d glossary
>                                     Table "glossary"
>  Attribute |         Type          |                      Modifier
> -----------+-----------------------+-----------------------------------------------------
>  glid      | integer               | not null default 
> nextval('glossary_glid_seq'::text)
>  glterm    | character varying(30) | not null
>  gldesc    | text                  |
> Indices: glossary_pkey,
>          glossary_term_index
> 
> nymr=#
> 
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

Re: sort / limit / range problem

From
Gary Stainburn
Date:
On Wednesday 05 Mar 2003 6:59 pm, you wrote:
> On Wed, 5 Mar 2003, Gary Stainburn wrote:
> > Hi folks,
> >
> > I've got a glossary table that I'm trying to render to HTML.  However,
> > I've got a problem when using order by and limit.  Below is an example of
> >  a psql session showing my problem. Anyone got any reasons why the last
> > select misses 'Driver'?
>
> You forgot ORDER BY glterm

Thanks you too, sometimes you can't see the wood for the trees

Gary

>
> > nymr=# select glterm from glossary where glterm like 'D%' order by
> > glterm; glterm
> > ----------------
> >  Dampers
> >  Dart
> >  Detonators
> >  Disposal
> >  Dome
> >  Draw Bar
> >  Driver
> >  Driving Wheels
> >  Duty Fitter
> > (9 rows)
> >
> > nymr=# select glterm from glossary where glterm > 'Driving Wheels' limit
> > 1; glterm
> > -------------
> >  Duty Fitter
> > (1 row)
> >
> > nymr=# select glterm from glossary where glterm > 'Draw Bar' limit 1;
> >      glterm
> > ----------------
> >  Driving Wheels
> > (1 row)
> >
> > nymr=# \d glossary
> >                                     Table "glossary"
> >  Attribute |         Type          |                      Modifier
> > -----------+-----------------------+-------------------------------------
> >---------------- glid      | integer               | not null default
> > nextval('glossary_glid_seq'::text)
> >  glterm    | character varying(30) | not null
> >  gldesc    | text                  |
> > Indices: glossary_pkey,
> >          glossary_term_index
> >
> > nymr=#

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000