SQL search multiple columns with priority

Question

I'm trying to implement the search in pset8 and want to find the most elegant and fast solution.

The way I have implemented it is to first of all check whether the search term is an int, and if so, search solely within the postal_code field. If it is not an int, it searches within place_name. Then, if there are less than 10 results, it does another search in admin_name1 to check against states. This is my code:

@app.route("/search")
def search():
    """Search for places that match query."""
    try:
        int(request.args.get("q"))
        postcode = True
    except:
        postcode = False

    q = request.args.get("q") + "%"
    if postcode:
        rows = db.execute("SELECT * FROM places WHERE postal_code LIKE :q LIMIT 10", q = q)
    else:
        rows = db.execute("SELECT * FROM places WHERE place_name LIKE :q LIMIT 10", q = q)
        if len(rows) < 10:
            newrows = db.execute("SELECT * FROM places WHERE admin_name1 LIKE :q LIMIT :limit", q = q, limit = 10 - len(rows))
            rows = rows + newrows
    return jsonify(rows)

The reason I did not use an OR clause is because I want the place_name matches to come first, so that if you search for 'New York' for example, all the city matches come before the state matches as that's probably more relevant. So what I ideally want to do is to perform a single SELECT statement which returns a maximum of 10 results where the place_name matches are returned first and the admin_name1 matches come after that. Is this possible!?

Answer 1

Well I found out the answer thanks to Stack Overflow. In case anybody's interested, this is what I did:

SELECT postal_code, place_name, admin_name1, latitude, longitude FROM (
    SELECT CASE
    WHEN place_name LIKE :q THEN 1 ELSE 2
    END as rank, * FROM places 
    WHERE place_name LIKE :q OR admin_name1 LIKE :q
) ORDER BY rank LIMIT 10