I have three queries as follows:
select * from orders where full_name like 'Mohammed Ali%' order by g_date desc
This will gives all names starting with Mohammed Ali (10 results)
select * from orders where full_name like '%Mohammed Ali%' order by g_date desc
All names have 'Mohammed Ali' in it (20 results)
select * from orders where full_name like '%Mohammed%Ali%' order by g_date desc
All names have Mohammed and Ali (100 results)
I would like to get results for all three queries, but the priority for the 1st query then the 2nd, and finally, the last. I don't want duplicate results.
I first did the following:
select * from (
select * from orders where full_name like 'Mohammed Ali%'order by g_date desc)
union
select * from (
select * from orders where full_name like '%Mohammed Ali%'order by g_date desc)
union
select * from (select * from orders where full_name like '%Mohammed%Ali%'order by g_date desc)
But, I got mixed results from the three queries :( . No duplicates - (100 results) Great!
Then, I tried the following:
select * from (
select *,0 as ord from orders where full_name like 'Mohammed Ali%')
union
select * from (
select *,1 as ord from orders where full_name like '%Mohammed Ali%')
union
select *,2 as ord from (select * from orders where full_name like '%Mohammed%Ali%')
order by ord,g_date desc
The first issue was fixed (Great). However, now I have duplicate results (10 + 20 + 100)
How can I get sorted results with no duplicates?
The only condition that you need in the WHERE clause is full_name LIKE '%Mohammed%Ali%'.
In the ORDER BY clause you can sort the returned rows by boolean expressions/conditions:
SELECT *
FROM orders
WHERE full_name LIKE '%Mohammed%Ali%'
ORDER BY full_name LIKE 'Mohammed Ali%' DESC, -- first all names starting with 'Mohammed Ali'
full_name LIKE '%Mohammed Ali%' DESC; -- then all names containing 'Mohammed Ali'
-- all the other names will be at the bottom of the resultset