I have real world mysql select problem with my website, I have members who's gender is either female or male, But they are not proportionally distributed, i.e 70% male, 30% female,
I want to do Select to the Display in my home page of the website, where
How do i achieve this via a Single Query ?
ex:
SELECT * FROM user u WHERE 1 ORDER BY rand() LIMIT 50
will not select the 50% ratio, i want, so please help if you can
Have you tried a union?
SELECT * FROM table WHERE gender = 1 LIMIT 25
UNION
SELECT * FROM table WHERE gender = 2 LIMIT 25
Could you try this?
SET @odd := 1;
SET @even := 2;
SELECT *
FROM
(
SELECT *
FROM
(
SELECT @odd := @odd + 2 AS ord, users.*
FROM users
WHERE sex = 'M'
ORDER BY RAND()
LIMIT 25
) x
UNION ALL
SELECT *
FROM
(
SELECT @even := @even + 2 AS ord, users.*
FROM users
WHERE sex = 'F'
ORDER BY RAND()
LIMIT 25
) y
) z
ORDER BY ord;
sample output
+------+------+------+
| ord | id | sex |
+------+------+------+
| 3 | 1 | M |
| 4 | 8 | F |
| 5 | 2 | M |
| 6 | 9 | F |
| 7 | 3 | M |
| 8 | 10 | F |
My First Approach was like this
SET @odd := 1;
SET @even := 2;
SELECT *
FROM
(
SELECT @odd := @odd + 2 AS ord, users.*
FROM users
WHERE sex = 'M'
ORDER BY RAND()
LIMIT 25
UNION ALL
SELECT @even := @even + 2 AS ord, users.*
FROM users
WHERE sex = 'F'
ORDER BY RAND()
LIMIT 25
) x
ORDER BY ord;
But I was getting an error as follows:
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY