I have a query like this:
SELECT @rank := @rank + 3 `rank`, id, subject, name
FROM quran, (select @rank := -2) q
WHERE MATCH (subject, name) AGAINST ('anything') and aye IN ("10")
UNION DISTINCT
SELECT @rank1 := @rank1 + 3 `rank`, id, subject, name
FROM quran, (select @rank1 := -1) q
WHERE MATCH (subject, name) AGAINST ('anything')
UNION ALL
SELECT @rank2 := @rank2 + 3 `rank`, id, subject, byA
FROM hadith, (select @rank2 := 0) q
WHERE MATCH (subject) AGAINST ('anything')
ORDER BY rank LIMIT 0, 11
Now I optimized my query and combined tow first SELECT
clause to one, like this: (because they have the same table name)
(SELECT @rank1 := @rank1 + 2 `rank`, id, subject, name
FROM quran, (select @rank1 := -1) q
WHERE MATCH (subject, name) AGAINST ('anything')
ORDER BY CASE WHEN aye IN ('10')
THEN 0
ELSE 1
END
)
UNION ALL
(SELECT @rank2 := @rank2 + 2 `rank`, id, subject, byA
FROM hadith, (select @rank2 := 0) q
WHERE MATCH (subject) AGAINST ('anything')
)
ORDER BY rank LIMIT 0, 11
But I don't know why the sort of result is not identical with the first query. Why? And how can I fix it?
Edit: Here is some examples:
// quran // hadith
+----+---------+--------+ +----+---------+-------+
| id | subject | name | | id | subject | byA |
+----+---------+--------+ +----+---------+-------+
| 1 | hello | jack | | 1 | blue | jack |
| 2 | blue | peter | | 2 | how | hello |
| 3 | jack | red | | 3 | jack | blue |
| 4 | back | blue | +----+---------+-------+
| 10 | jack | how |
+----+---------+--------+
Now, I want this output: So first priority is that $number
, and then subject
column and then name
column, Also the result is alternating for both tables.
$anything = 'jack', $number = 10
+----+---------+--------+
| id | subject | name |
+----+---------+--------+
| 10 | jack | how |
| 3 | jack | blue |
| 3 | jack | red |
| 1 | blue | jack |
| 1 | hello | jack |
+----+---------+--------+
I'm not saying this is the best way to go about things, but it is the least modification to your existing attempts.
Actually, I am not positive you can merge the first two unioned queries and get the same results. In the original query, with
UNION DISTINCT
and the separate computation of rank in the original, records that satisfy theaye IN ("10")
criteria will probably often appear twice (but with different rank values).