CODEWITHSUNDEEP
sqloracle-database
user964147
user964147

Reputation: 739

Database query to search using address

I am developing a search functionality to search for dealers by their address(i.e. by postcode(zip) or by name or by city)for my project. user will be provided with only one html input field to enter the search term (users can enter only one search term at a time, i.e. either zip code or name or city (combinations are not allowed)). The conditions to develop a query are as below

  1. When user search by zip code, matched results should be displayed in descending order
  2. When user search by name matched results should be display alphabetically
  3. Same as city name

Some times there is a possibility that name can also contains zip code in that case results should be shorted by zip code.

I tried like this 

select city, postcode, name
  from dealers
  where name ='xyz' OR
        postcode ='xyz' OR
        city='XYZ'
  ORDER BY postcode desc

But I want 'ORDER BY' for every condition. for example 'xyz' matches to the first name I want to sort the result in ascending order. How can I do that?

Upvotes: 0

Views: 1825

Answers (2)

APC
APC

Reputation: 146309

Life would be easier if all your terms were sorted in ascending order.

I assume that your actual application is using a variable rather than a hard-coded string. So the solution should look something like this:

  select city, postcode, name
  from dealers
  where name = p_search_term OR
        postcode = p_search_term OR
        city = p_search_term
  ORDER BY case when postcode = p_search_term then p_search_term else 1 end desc
           , case when name = p_search_term then p_search_term else city end asc

Upvotes: 2

Manolis Agkopian
Manolis Agkopian

Reputation: 1094

In MySQL you do it like this:

     "SELECT `zip`, `name`, `city` FROM `dealers` WHERE `zip` = '$zip' ORDER BY `zip` DESC"
     "SELECT `zip`, `name`, `city` FROM `dealers` WHERE `name` = '$name' ORDER BY `name`"
     "SELECT `zip`, `name`, `city` FROM `dealers` WHERE `city` = '$city' ORDER BY `city`"

I don't use Oracle but I think that it is the same. (This code assumes that zip is a varchar, if it is an integer value just remove the ' ')

Upvotes: 0

Related Questions