How to display random record with respect to category/position in mySQL

If you have a requirement to display a random record with respect to the category or position on your web page then you may have to play with your SQL to accomplish the task.

To accomplish this I am taking 2 tables for this example:

We will have a base table by the name of vehicles. You can assume that this table houses all the vehicles data.

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `sno` INT(11) NOT NULL,
  `veh_make_model` VARCHAR(255) NOT NULL,
  `veh_year` INT(4) NOT NULL,  
  `veh_mileage` INT(5) NOT NULL,    
  `veh_color` VARCHAR(50) NULL,  
  PRIMARY KEY (`id`)
) ENGINE=MYISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `vehicles` (`sno`, `veh_make_model`, `veh_year`, `veh_mileage`, `veh_color`) VALUES
(23003, 'Toyota Avanza', '2005', '34000', 'Black'),
(32433, 'Toyota Vitz', '2011', '14000', 'White'),
(45654, 'Toyota Avalon', '2010', '40000', 'Blue'),
(65599, 'Toyota Camry', '2007', '14000', 'Orange'),
(40006, 'Toyota BB', '2006', '36700', 'Silver'),
(23486, 'Toyota Highlander', '2003', '37320', 'Yellow'),
(23996, 'Toyota Landcruiser', '2002', '43500', 'Wine Red'),
(59993, 'Toyota Landcruiser Prado', '2001', '65600', 'Black'),
(50032, 'Toyota Aqua', '2000', '87000', 'Black'),
(49227, 'Toyota Corolla', '2004', '99000', 'Black'),
(49992, 'Toyota Corolla Fielder', '2008', '32000', 'Black'),
(50137, 'Toyota Corolla Axio', '2009', '66700', 'Black'),
(23322, 'Toyota Allion', '2012', '56500', 'Black'),
(87654, 'Toyota Harrier', '2005', '75600', 'Black'),
(50167, 'Toyota Fortuner', '2003', '36740', 'Black'),
(67565, 'Toyota Avanza', '2014', '21700', 'Black'),
(50132, 'Toyota Vitz', '2013', '34000', 'Black');

Next we will create another table which are the popular vehicles with respect to their position on the web page. This table will have 2-3 records with respect to position. So if you have 5 positions then it will most likely have 10-15 records.

CREATE TABLE IF NOT EXISTS `popular_vehicles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `position` TINYINT(2) NOT NULL,
  `sno` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `popular_vehicles` (`id`, `position`, `sno`) VALUES
(1, 1, 45654),
(3, 1, 50132),
(4, 2, 40006),
(5, 2, 50137),
(6, 3, 50167),
(7, 3, 49227);

Now we will build the actual SQL which will give us a random record for each position

SELECT * FROM (
  	          SELECT
	          pv.position, v.*
	          FROM popular_vehicles pv
	          INNER JOIN vehicles v ON pv.sno = v.sno
	          ORDER BY RAND()
	      ) AS temp_table
GROUP BY position 

I hope the above helped.

Please follow and like us:

Be the first to comment

Leave a Reply

Your email address will not be published.


*