The SQL Query to power Google Suggest

6

You probably know Google Suggest or one of its derivatives. An input item on a web-page or another user interface where you can enter a value that all of a sudden becomes active and helpful by displaying a number of suggestions. For example a Country field that we can enter the country into within the previously selected Region (Europe in this case): 

After entering Bel, the suggestions are displayed as shown in the screenshot. This article describes the SQL we could use for producing the list of suggestions.....

The data returned by the query should satisfy these conditions:

  • only countries can be shown that are equal to what was entered, start with the value that was entered or come alfabetically after the value that was entered
  • if there is no value at all that starts with the string entered, then no values may be displayed 
  • no more than five values can be shown

When we type an extra g, Belarus disappears as suggestion since it does not start with Belg. Finland is added as fifth (and not very logical) suggestion.

When we enter Belgr, we see no suggestions at all, since no single value starts with Belgr.

 

The data displayed in this example is retrieved from two tables: REGIONS and COUNTRIES that are linked through a REGION_ID column. The join query looks like this:

 select region_name<br /> ,      country_name<br /> from   regions<br />        join<br />        countries<br /> using  (region_id)<br /> order<br /> by     region_name<br /> ,      country_name

If we only want to see countries in Region=Europe, we have the following query:

select country_name<br />from   regions<br />       join<br />       countries<br />       using  (region_id)<br />where  region_name = 'Europe' <br />

 

Now we want to create the Google Suggest Query, based on the requirements indicated above. The following query does exactly what we need:

select country_name<br />from   ( select country_name<br />         ,      first_value( country_name) <br />                over (order by country_name) first_country_value<br />         from ( select country_name<br />                from   regions<br />                join<br />                countries<br />                using  (region_id)<br />                where  region_name = 'Europe' <br />              )<br />         where  country_name &gt;= 'VALUE_ENTERED_BY_USER'<br />         order<br />         by     country_name<br />       )  <br />where  first_country_value like 'VALUE_ENTERED_BY_USER%'<br />and    rownum &lt; 6<br />/<br />&nbsp;

Some results of using this query:

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

6 Comments

  1. I’d thought of something like this at first:

    select omschrijving from bestemmingen
    where soundex(omschrijving) = soundex( ‘Santa’ )
    /

    OMSCHRIJVING
    —————————————————–
    Sand
    Sand
    Sannat
    Schwendau
    Sunny Day

    5 rijen zijn geselecteerd.

    Verstreken: 00:00:00.06

    Advantage: Finland would not appear in this list.
    Disadvantage: Soundex only works fine for English words.

    But, I’ve once written a Dutch algorithm for it so there is hope ;)