Postgresql union query with priority on one query

Question

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

Hello Tomer, next time try to provide a SqlFiddle so we can help you faster and better — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 22, 2015 at 21:45
What I would try is to assign your own priority value to both sub-queries, and them do a select from results by order with a specified limit. Perhaps there are better solutions, but this is something that comes to mind. — kos
– kos, Commented Aug 22, 2015 at 21:47

Juan Carlos Oropeza · Accepted Answer · 2015-08-22 21:59:28Z

3

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

edited Aug 22, 2015 at 21:59

answered Aug 22, 2015 at 21:47

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql union query with priority on one query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related