combine two results into one result set mysql

Question

I have two queries one will return data ordered by likes and in the user city the other one return data by the distance . so if query 1 return : id 1,2,3 (order by likes) and query 2 return : id 4,5,6 (order by distance) i need the final set results to be 1,2,3,4,5,6 i've tried to do union between the two queries but it's not working. any other suggestions ?

(SELECT DISTINCT ID, 'a' as type,... FROM table1 GROUP BY ID ORDER BY likesDESC ) union all( SELECT DISTINCT ID, 'b' as type,....FROM table1 GROUP BY ID ORDER BY distance) — Maisam Mansour, Nov 02 '15 at 10:56
but the result set returned in the first query and the result set in the second query is different from the final result — Maisam Mansour, Nov 02 '15 at 10:58

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

You can use left join or union according to this link. Union ALL also works like you can see here.

Example: SELECT 1 UNION ALL SELECT 2

edited May 23 '17 at 11:46

Community

1
1

answered Nov 02 '15 at 10:59

Keurdange

1
2

but this still won't return as i wish . the 1,2,3,4,5,6 . so far it return 7,8,9,10,11,12... – Maisam Mansour Nov 02 '15 at 11:07
Post your full query here please – Keurdange Nov 02 '15 at 11:32

score 0 · Answer 2 · answered Nov 02 '15 at 11:25

the solution was to put a limit to each query then the union will work correct : (SELECT DISTINCT ID, 'a' as type,... FROM table1 GROUP BY ID ORDER BY likesDESC limit 50) union all( SELECT DISTINCT ID, 'b' as type,....FROM table1 GROUP BY ID ORDER BY distance limit 50) order by type asc.

combine two results into one result set mysql

2 Answers2