Thread: ORDER BY what?

ORDER BY what?

From

Martín Marqués

Date:

11 June 2001, 14:36:55

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
-----------------------------------------------------------------

Re: ORDER BY what?

From

will trillich

Date:

11 June 2001, 21:59:02

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!

pg_dumpall anomaly

From

"Tim Mickol"

Date:

11 June 2001, 23:03:00

Experienced some pg_dumpall weirdness this past weekend when dumping a
cluster to propagate it to some other servers;
here are the first 18 lines of the pg_dumpall output:
--
-- pg_dumpall (7.1.2)
--
\connect template1
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

db1|db1_dba|
db2|db2_dba|
db3|db3_dba|
postgres|postgres|
template0|postgres|
template1|postgres|Default template database
CREATE USER "db1_dba" WITH SYSID 10 PASSWORD 'dba_passwd1' CREATEDB
CREATEUSER;
CREATE USER "db1_user" WITH SYSID 11 PASSWORD 'usr_passwd1' NOCREATEDB
NOCREATEUSER;
CREATE USER "db2_dba" WITH SYSID 20 PASSWORD 'dba_passwd2' CREATEDB
CREATEUSER;
CREATE USER "db2_user" WITH SYSID 21 PASSWORD 'user_passwd2' NOCREATEDB
NOCREATEUSER;
CREATE USER "db3_dba" WITH SYSID 30 PASSWORD 'dba_passwd3' CREATEDB
CREATEUSER;
CREATE USER "db3_usr" WITH SYSID 31 PASSWORD 'usr_passwd3' NOCREATEDB
NOCREATEUSER;

psql -f pg_cluster_dump.sql template1;

systax error on line 13 near "db1"

what are the: <schema_name>|<schema_owner>| directives all about? They are
obviously(?) causing a syntax error in the CREATE USER line that follows
them - user "db1_dba" was not being created.  A kludgely solution was to
simply duplicate the line.  The question is, why did those lines get
inserted?

I did successfully propagate the cluster and found that all subsequent
pg_dumpall executions were fine; in other words I (fortunately) could not
repro the problem.

Any obvious explanations?

tjm

"All usernames and passwds in this program have been changed to protect the
innocent"

Re: pg_dumpall anomaly

From

Tom Lane

Date:

12 June 2001, 07:05:03

"Tim Mickol" <tmickol@combimatrix.com> writes:
> what are the: <schema_name>|<schema_owner>| directives all about?

Good question.  They look like an abbreviated select from pg_database,
but pg_dumpall shouldn't be doing any such thing.  Is it possible you've
got a locally modified pg_dumpall script?

            regards, tom lane

Re: [SQL] ORDER BY what?

From

David Olbersen

Date:

12 June 2001, 18:45:59

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

RE: pg_dumpall anomaly

From

"Tim Mickol"

Date:

12 June 2001, 20:11:43

no possibility that it was a locally modified script.  pg_dumpall came
straight out of the 7.1.2-2 PGDG binary rpm

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, June 12, 2001 12:11 AM
To: tmickol@combimatrix.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_dumpall anomaly

"Tim Mickol" <tmickol@combimatrix.com> writes:
> what are the: <schema_name>|<schema_owner>| directives all about?

Good question.  They look like an abbreviated select from pg_database,
but pg_dumpall shouldn't be doing any such thing.  Is it possible you've
got a locally modified pg_dumpall script?

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] ORDER BY what?

From

Martín Marqués

Date:

12 June 2001, 21:03:57

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
-----------------------------------------------------------------

Re: [SQL] ORDER BY what?

From

Bruno Wolff III

Date:

13 June 2001, 12:13:43

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.

Re: [SQL] ORDER BY what?

From

Martín Marqués

Date:

13 June 2001, 13:54:42

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
-----------------------------------------------------------------

Re: [SQL] ORDER BY what?

From

Bruno Wolff III

Date:

13 June 2001, 14:17:21

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.

Re: Re: [SQL] ORDER BY what?

From

Lamar Owen

Date:

13 June 2001, 15:31:02

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

Re: ORDER BY what?

From

"postgres"

Date:

20 June 2001, 16:33:18

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