pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
ORDER BY what?
9+ messages / 6 participants
[nested] [flat]

* ORDER BY what?
@ 2001-06-11 08:19 Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-11 22:37 ` Re: ORDER BY what? will trillich <will@serensoft.com>
  2001-06-11 23:23 ` Re: [SQL] ORDER BY what? David Olbersen <dave@slickness.org>
  2001-06-13 15:34 ` Re: ORDER BY what? postgres <harry@iobion.com>
  0 siblings, 3 replies; 9+ messages in thread

From: Martín Marqués @ 2001-06-11 08:19 UTC (permalink / raw)
  To: pgsql-sql; pgsql-general@postgresql.org

I have a quite complex select query on postgres (obviously :-) ) using PHP 
(which is not the problem at the moment) which has some text searches using 
LIKE.
My question is: "How can I write an ORDER BY statment so that it's ordered by 
the amount of matches it gets?"
Is this posible without getting ichy with the programing?

Saludos.... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------



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

* Re: ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-11 22:37 ` will trillich <will@serensoft.com>
  2 siblings, 0 replies; 9+ messages in thread

From: will trillich @ 2001-06-11 22:37 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

On Mon, Jun 11, 2001 at 11:19:31AM +0300, Mart?n Marqu?s wrote:
> I have a quite complex select query on postgres (obviously :-) ) using PHP 
> (which is not the problem at the moment) which has some text searches using 
> LIKE.
> My question is: "How can I write an ORDER BY statment so that it's ordered by 
> the amount of matches it gets?"
> Is this posible without getting ichy with the programing?

<guess>

not likely. :) sounds like programming is gonna be
required.

you may be better off with some of the "contrib" features
including full-text-index (of which i have no working
knowledge). or a fancy pl/pgsql function. or a mod_perl glue
layer to coordinate your results...

</guess>

and using LIKE can be a serious performance drag if you use the
leading percent: "...LIKE '%this%'..." can't use any indexes
so it'll scan the whole database stem to stern. if you can stick
with "...LIKE 'this%'..." it'll at least be able to use the
indexes.

-- 
I figure: if a man's gonna gamble, may as well do it
without plowing.   -- Bama Dillert, "Some Came Running"

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!



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

* Re: [SQL] ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-11 23:23 ` David Olbersen <dave@slickness.org>
  2001-06-12 12:53   ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2 siblings, 1 reply; 9+ messages in thread

From: David Olbersen @ 2001-06-11 23:23 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql; pgsql-general@postgresql.org

On Mon, 11 Jun 2001, Martín Marqués wrote:

> My question is: "How can I write an ORDER BY statment so that it's ordered by
> the amount of matches it gets?"

Your question is a bit unclear, what is "it"?
Specifically, what are the "it's" that are to be ordered, and which "it" gets
matches?

-- Dave




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

* Re: [SQL] ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-11 23:23 ` Re: [SQL] ORDER BY what? David Olbersen <dave@slickness.org>
@ 2001-06-12 12:53   ` Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-13 13:16     ` Re: [SQL] ORDER BY what? Bruno Wolff III <bruno@wolff.to>
  0 siblings, 1 reply; 9+ messages in thread

From: Martín Marqués @ 2001-06-12 12:53 UTC (permalink / raw)
  To: David Olbersen <dave@slickness.org>; +Cc: pgsql-sql; pgsql-general@postgresql.org

On Mar 12 Jun 2001 02:23, David Olbersen wrote:
> On Mon, 11 Jun 2001, Martín Marqués wrote:
> > My question is: "How can I write an ORDER BY statment so that it's
> > ordered by the amount of matches it gets?"
>
> Your question is a bit unclear, what is "it"?
> Specifically, what are the "it's" that are to be ordered, and which "it"
> gets matches?

OK, let me give an example:

select * from tab1 where col1 like '%word%' or col2 like '%word%' and col3 
like '%word%'

and I want to order by the amopunt of matches that a matching register has. 
Something like, if it matches all the ORs, then it should go first, and if it 
matches only one of the ORs it should go last.
Or maybe even have several words trying to match one of the columns.

Saludos... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------



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

* Re: [SQL] ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-11 23:23 ` Re: [SQL] ORDER BY what? David Olbersen <dave@slickness.org>
  2001-06-12 12:53   ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-13 13:16     ` Bruno Wolff III <bruno@wolff.to>
  2001-06-13 08:26       ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  0 siblings, 1 reply; 9+ messages in thread

From: Bruno Wolff III @ 2001-06-13 13:16 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: David Olbersen <dave@slickness.org>; pgsql-sql; pgsql-general@postgresql.org

On Tue, Jun 12, 2001 at 03:53:22PM +0300,
  Martín Marqués <martin@bugs.unl.edu.ar> wrote:
> On Mar 12 Jun 2001 02:23, David Olbersen wrote:
> > On Mon, 11 Jun 2001, Martín Marqués wrote:
> > > My question is: "How can I write an ORDER BY statment so that it's
> > > ordered by the amount of matches it gets?"
> >
> > Your question is a bit unclear, what is "it"?
> > Specifically, what are the "it's" that are to be ordered, and which "it"
> > gets matches?
> 
> OK, let me give an example:
> 
> select * from tab1 where col1 like '%word%' or col2 like '%word%' and col3 
> like '%word%'
> 
> and I want to order by the amopunt of matches that a matching register has. 
> Something like, if it matches all the ORs, then it should go first, and if it 
> matches only one of the ORs it should go last.
> Or maybe even have several words trying to match one of the columns.

You could do this by computing a value based on the number of parts
that matched and order by it.



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

* Re: [SQL] ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-11 23:23 ` Re: [SQL] ORDER BY what? David Olbersen <dave@slickness.org>
  2001-06-12 12:53   ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-13 13:16     ` Re: [SQL] ORDER BY what? Bruno Wolff III <bruno@wolff.to>
@ 2001-06-13 08:26       ` Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-13 14:57         ` Re: [SQL] ORDER BY what? Bruno Wolff III <bruno@wolff.to>
  2001-06-13 15:35         ` Re: Re: [SQL] ORDER BY what? Lamar Owen <lamar.owen@wgcr.org>
  0 siblings, 2 replies; 9+ messages in thread

From: Martín Marqués @ 2001-06-13 08:26 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: David Olbersen <dave@slickness.org>; pgsql-sql; pgsql-general@postgresql.org

On Mié 13 Jun 2001 16:16, Bruno Wolff III wrote:
> On Tue, Jun 12, 2001 at 03:53:22PM +0300,
>
> >
> > OK, let me give an example:
> >
> > select * from tab1 where col1 like '%word%' or col2 like '%word%' and
> > col3 like '%word%'
> >
> > and I want to order by the amopunt of matches that a matching register
> > has. Something like, if it matches all the ORs, then it should go first,
> > and if it matches only one of the ORs it should go last.
> > Or maybe even have several words trying to match one of the columns.
>
> You could do this by computing a value based on the number of parts
> that matched and order by it.

Could you give me a hint on this? Do I have to use PLSQL? Triggers? Something 
else?

Thanks for the responce.

Saludos... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------



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

* Re: [SQL] ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-11 23:23 ` Re: [SQL] ORDER BY what? David Olbersen <dave@slickness.org>
  2001-06-12 12:53   ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-13 13:16     ` Re: [SQL] ORDER BY what? Bruno Wolff III <bruno@wolff.to>
  2001-06-13 08:26       ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-13 14:57         ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 9+ messages in thread

From: Bruno Wolff III @ 2001-06-13 14:57 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; +Cc: David Olbersen <dave@slickness.org>; pgsql-sql; pgsql-general@postgresql.org

On Wed, Jun 13, 2001 at 11:26:54AM +0300,
  Martín Marqués <martin@bugs.unl.edu.ar> wrote:
> On Mié 13 Jun 2001 16:16, Bruno Wolff III wrote:
> > On Tue, Jun 12, 2001 at 03:53:22PM +0300,
> >
> > >
> > > OK, let me give an example:
> > >
> > > select * from tab1 where col1 like '%word%' or col2 like '%word%' and
> > > col3 like '%word%'
> > >
> > > and I want to order by the amopunt of matches that a matching register
> > > has. Something like, if it matches all the ORs, then it should go first,
> > > and if it matches only one of the ORs it should go last.
> > > Or maybe even have several words trying to match one of the columns.
> >
> > You could do this by computing a value based on the number of parts
> > that matched and order by it.
> 
> Could you give me a hint on this? Do I have to use PLSQL? Triggers? Something 
> else?
> 
> Thanks for the responce.

You can use the case statement to check logical tests and return different
values depending on which ones are true. This expression can be used in
the order by clause.



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

* Re: Re: [SQL] ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-11 23:23 ` Re: [SQL] ORDER BY what? David Olbersen <dave@slickness.org>
  2001-06-12 12:53   ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
  2001-06-13 13:16     ` Re: [SQL] ORDER BY what? Bruno Wolff III <bruno@wolff.to>
  2001-06-13 08:26       ` Re: [SQL] ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-13 15:35         ` Lamar Owen <lamar.owen@wgcr.org>
  1 sibling, 0 replies; 9+ messages in thread

From: Lamar Owen @ 2001-06-13 15:35 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; Bruno Wolff III <bruno@wolff.to>; +Cc: David Olbersen <dave@slickness.org>; pgsql-sql; pgsql-general@postgresql.org

On Wednesday 13 June 2001 04:26, Martín Marqués wrote:
> On Mié 13 Jun 2001 16:16, Bruno Wolff III wrote:
> > On Tue, Jun 12, 2001 at 03:53:22PM +0300,
> > > select * from tab1 where col1 like '%word%' or col2 like '%word%' and
> > > col3 like '%word%'

> > > and I want to order by the amopunt of matches that a matching register
> > > has. Something like, if it matches all the ORs, then it should go
> > > first, and if it matches only one of the ORs it should go last.
> > > Or maybe even have several words trying to match one of the columns.

> > You could do this by computing a value based on the number of parts
> > that matched and order by it.

> Could you give me a hint on this? Do I have to use PLSQL? Triggers?
> Something else?

SELECT *,
	((CASE WHEN col1 like '%word%'
		THEN 1
		ELSE 0
	    END)
	+
	(CASE WHEN col2 like '%word%'
		THEN 1
		ELSE 0
	    END)
	+
	(CASE WHEN col3 like '%word%'
		THEN 1
		ELSE 0
	    END))
	AS matches
FROM tab1
WHERE 
	col1 like '%word%' or 
	col2 like '%word%' and
	col3 like '%word%'
ORDER BY matches desc;

:-)

Shouldn't be terribly hard to generate this programmatically, but it _is_ a 
bear to type by hand.  If all conditions were guaranteed to be OR (you have 
an AND up there) you could replace the where clause in my example with:

WHERE matches > 0

This counting could slow your queries down significantly, though.  You'd have 
to try performance testing of it.

If you wanted the top fifty of these, you could use LIMIT appropriately.

Been there, done that.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11



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

* Re: ORDER BY what?
  2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
@ 2001-06-13 15:34 ` postgres <harry@iobion.com>
  2 siblings, 0 replies; 9+ messages in thread

From: postgres @ 2001-06-13 15:34 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

Try to give a alias of the value and order by the alias:

 select count(spotted_gene_id) as bbb from spot_nav_simple group by
spotted_gene_id order by  bbb desc limit 10;

Hope this is what you ask for.


"Martín Marqués" <martin@bugs.unl.edu.ar> wrote in message
news:01061111193103.29653@bugs...
> I have a quite complex select query on postgres (obviously :-) ) using PHP
> (which is not the problem at the moment) which has some text searches
using
> LIKE.
> My question is: "How can I write an ORDER BY statment so that it's ordered
by
> the amount of matches it gets?"
> Is this posible without getting ichy with the programing?
>
> Saludos.... :-)
>
> --
> Cualquiera administra un NT.
> Ese es el problema, que cualquiera administre.
> -----------------------------------------------------------------
> Martin Marques                  |        mmarques@unl.edu.ar
> Programador, Administrador      |       Centro de Telematica
>                        Universidad Nacional
>                             del Litoral
> -----------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org





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


end of thread, other threads:[~2001-06-13 15:35 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-11 08:19 ORDER BY what? Martín Marqués <martin@bugs.unl.edu.ar>
2001-06-11 22:37 ` will trillich <will@serensoft.com>
2001-06-11 23:23 ` David Olbersen <dave@slickness.org>
2001-06-12 12:53   ` Martín Marqués <martin@bugs.unl.edu.ar>
2001-06-13 13:16     ` Bruno Wolff III <bruno@wolff.to>
2001-06-13 08:26       ` Martín Marqués <martin@bugs.unl.edu.ar>
2001-06-13 14:57         ` Bruno Wolff III <bruno@wolff.to>
2001-06-13 15:35         ` Lamar Owen <lamar.owen@wgcr.org>
2001-06-13 15:34 ` postgres <harry@iobion.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