Thread: doc on searching, and sorting

Am Mo, 2003-08-25 um 18.53 schrieb Luis H.:
>
> In the absence of that, I'm currently trying to figure out if it is
> feasible to sort my matches as follows: I have column A and column B,
> I execute a search for certain words in A OR certain words in B, I'd
> like to sort it so that documents that match both A and B appear
> first, then A, then B. Or how about sorting such that the rows that
> matched the most words in the search string appear first? I dont know
> if such sorting would require joins and subqueries such that it would
> take up a lot of resources. Any tips would be appreciated!

Here's an example:
BTW: TRUE will be listed after FALSE, so we have to use DESC in the
ORDER BY clause.

Forget about the UNION ALL stuff, it's just there to give you an example
table with 3 rows and 2 cols.

SELECT
    *
FROM
    (
        SELECT 1 as a, 2 as b UNION ALL
        SELECT 1 as a, 1 as b UNION ALL
        SELECT 1 as a, 0 as b
    ) AS test_table
ORDER BY
    (a ILIKE '%keyword%' and b ILIKE '%keyword%') DESC;

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Am Mo, 2003-08-25 um 18.53 schrieb Luis H.:
> Hi,
> I'm looking for a good treatment of searching through tables in
> PostgreSQL. I've read the doc, so I can build searches using
> LIKE/SIMILAR TO/~ , but it would be really useful if I could read up
> on strategies for executing queries (both for good results, and for
> efficiency).
>
> In the absence of that, I'm currently trying to figure out if it is
> feasible to sort my matches as follows: I have column A and column B,
> I execute a search for certain words in A OR certain words in B, I'd
> like to sort it so that documents that match both A and B appear
> first, then A, then B. Or how about sorting such that the rows that
> matched the most words in the search string appear first? I dont know
> if such sorting would require joins and subqueries such that it would
> take up a lot of resources. Any tips would be appreciated!
>
> Thanks.
> - Luis
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de