CODEWITHSUNDEEP
sqlpostgresql
Tomer Shemesh
Tomer Shemesh

Reputation: 13405

Postgresql union query with priority on one query

So I have a table with 2 columns

class_id | title          
CS124    | computer tactics          
CS101    | intro to computers          
MATH157  | math stuff          
CS234    | other CS stuff          
FRENCH50 | TATICS of french          
ENGR101  | engineering ETHICS      
OTHER1   | other CS title

I want to do a sort of smart search for auto complete where a user searches for something.

Lets say they type 'CS' into the box I want to search using both the class_id and title with a limit of lets say 5 for this example. I first want to search for class_ids like 'CS%' with a limit of 5 ordered by class_id. This will return the 3 cs classes.

Then if there is any room left in the limit I want to search using title like '%CS% and combine them but have the class_id matches be first, and make sure that duplicates are removed from the bottom like like cs234 where it would match on both queries.

So the end result for this query would be 

CS101    | intro to computers          
CS124    | computer tactics          
CS234    | other CS stuff          
ENGR101  | engineering ETHICS          
FRENCH50 | TATICS of french

I am trying to do something like this

(select * from class_infos
 where LOWER(class_id) like LOWER('CS%')
 order by class_id)

union

(select * from class_infos
 where LOWER(title) like LOWER('%CS%')
 order by class_id)

limit 30

But it is not putting them in the right order or make the class id query have priority. Anyone have any suggestions

Here is the sqlfiddle http://sqlfiddle.com/#!15/5368b

Upvotes: 1

Views: 1321

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Have you try something like this?

SQL Fiddle Demo

SELECT *
FROM
  (
    (select 1 as priority, * 
     from class_infos
     where LOWER(class_id) like LOWER('CS%'))
  union
    (select 2 as priority, * 
     from class_infos
     where 
             LOWER(title) like LOWER('%CS%')
     and not LOWER(class_id) like LOWER('CS%') 
    )
  ) as class
ORDER BY priority, class_id
limit 5

Upvotes: 3

Related Questions