Postgres Pro
Downloads
Home   >   mailing lists

Thread: ORDER BY what?

ORDER BY what?

From
Martín Marqués
Date:
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
David Olbersen
Date:
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: ORDER BY what?

From
Martín Marqués
Date:
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: ORDER BY what?

From
Martín Marqués
Date:
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: [GENERAL] Re: ORDER BY what?

From
Lamar Owen
Date:
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
Bruno Wolff III
Date:
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: ORDER BY what?

From
Bruno Wolff III
Date:
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.