How to select two random rows with same category in MySQL

Question

I am beginner in javascript and nodejs, and not having much knowledge of mysql. These are the column names of my MySQL table whose name is cars:

Name
model
TransmissionType
EngineSize
VehicleSize
BodyType

I want to randomly select Name and model from cars, but the category must match which will be decided by the other four coloumns: TransmissionType, EngineSize, VehicleSize and Body Type, in such a way that randomly selected cars must share same values for engine, TransmissionType, VehicheSize and BodyType. Output should be in two rows only.

Engine size has values: 3.2, 3.5, 1.6, 5.2, 2.0, 2.2

TransmissionType has values: Manual and Automatic

VehicleSize has values: Compact, MidSize and Large.

BodyType has values: Car, van and truck.

What query should I write to achieve this? Please help.

I did this, and the query is working fine, but there are too many sub-queries are running in it, is it possible to achieve this using not more den 2-3 sub-queries?

SELECT Name, model 
FROM cars 
WHERE EngineSize IN (SELECT * FROM (SELECT EngineSize FROM cars  ORDER BY RAND( ) LIMIT 1 ) AS A)  
AND TransmissionType IN (SELECT * FROM (SELECT TransmissionType  FROM cars ORDER BY RAND( ) LIMIT 1 ) AS B)
AND BodyType IN (SELECT * FROM (SELECT BodyType  FROM cars ORDER BY RAND( ) LIMIT 1 ) AS C)   
AND VehicleSize IN (SELECT * FROM (SELECT VehicleSize` FROM cars ORDER BY RAND( ) LIMIT 1 ) AS D)    
LIMIT 2

Nadeem_MK · Answer 1 · 2013-10-08T12:16:46.587

1

If you are in search of a list of vehicles matching certain categories, you can do this;

SELECT Name, model, Id * RAND() AS random_no
FROM   cars
WHERE  EngineSize = '2.2'
AND    TransmissionType = 'Automatic'
AND    VehicleSize = 'Large'
AND    BodyType = 'Car'
ORDER by random_no
LIMIT 1

And if you're doing it dynamically, you should pass the values (hardcoded in the above example) by parameters from your javascript.

edited Oct 08 '13 at 12:16

answered Oct 08 '13 at 09:45

Nadeem_MK

7,533
7
50
61

Actually my code needs random rows, this query will show a specific result. – Akshat Oct 08 '13 at 09:53
Add `ORDER BY RAND() LIMIT 1` – Nadeem_MK Oct 08 '13 at 09:55
Do you have an Id in your table? – Nadeem_MK Oct 08 '13 at 09:57
Yes, ID filed is there in the table. – Akshat Oct 08 '13 at 10:00
Database is very huge, I cannot pass parameters using javascript. It will have to be in SQL query alone as per requirement. – Akshat Oct 08 '13 at 10:09
So, you'll have to hardcode your query as the example. – Nadeem_MK Oct 08 '13 at 12:17

score 0 · Answer 2 · edited May 23 '17 at 12:21

SELECT Name, model
FROM   cars
WHERE  EngineSize = '2.2'
AND    TransmissionType = 'Automatic'
AND    VehicleSize = 'Large'
AND    BodyType = 'Car'
ORDER by RAND()
LIMIT 2

This select will select all results fitting the where clause, reorder the results in a random way, then limit the return to the first 2 fitting the criteria. See this blog, these instructions, and this SO post for more details.

How to select two random rows with same category in MySQL

2 Answers2