mysql – list the records in a table that appear most recently

definitely sql is my nemesis…

Here goes.

I need to list the employees whose gender is compatible with what else appears in the same table. Theoretically, a subquery is to be made for this selection (as the exercise proposal says) and I researched some ways to select the value that appears the most, but I can’t implement it in this subquery in a way that works.

What i have until here is

SELECT name as Nome, sex as Sexo FROM employee WHERE sex = (SELECT e1.sex, count(e1.sex) FROM employee e1 group by e1.sex LIMIT 1);

The error returned is that

Error Code: 1241. Operand should contain 1 column(s)    0.125 sec

Any deal?

THE DATA DUMP AND EXPECTED RESULTS

DROP TABLE IF EXISTS `department`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `department`
--

LOCK TABLES `department` WRITE;
/*!40000 ALTER TABLE `department` DISABLE KEYS */;
INSERT INTO `department` VALUES (1,'Desenvolvimento'),(2,'Financeiro'),(3,'Contabilidade'),(4,'Vendas'),(5,'Recursos Humanos'),(6,'DBA'),(7,'Administração Geral');
/*!40000 ALTER TABLE `department` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `employee`
--

DROP TABLE IF EXISTS `employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `payment` decimal(11,2) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_departamentoempregado` (`department_id`),
  CONSTRAINT `FK_departamentoempregado` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `employee`
--

LOCK TABLES `employee` WRITE;
/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `employee` VALUES (1,'João da Silva',1,1000.00,'2000-03-27',1),(2,'Maria Silveira',2,1000.00,'2000-03-27',1),(3,'Lidia Pereira',2,1200.00,'2000-03-27',1),(4,'Victor Oliveira',1,1500.00,'2000-03-27',1),(5,'Paulo Silva Campos',1,5000.00,'2000-03-27',2),(6,'Pedro Campos',1,10000.00,'2000-03-27',2),(7,'José da Silva Oliveira',1,9500.00,'2000-03-27',2),(8,'João Carlos Pereira',1,15000.00,'2000-03-27',2),(9,'Ana Paula Amorim',2,20000.00,'2000-03-27',3),(10,'Antônia de Castro',2,2000.00,'2000-03-27',3),(11,'Elisa Silveira Campos',2,3000.00,'2000-03-27',3),(12,'Gilson Machado',1,1000.00,'2000-03-27',3),(13,'Andreia Vitoria Silva',2,1200.00,'2000-03-27',4),(14,'Vicente da Rosa Silva',1,2200.00,'2000-03-27',4),(15,'Juliana Oliveira',2,3500.00,'2000-03-27',4),(16,'Marlon da Silva',1,2400.00,'2000-03-27',4),(17,'Silvio Luis Oliveira',1,1800.00,'2000-03-27',5),(18,'Roberto Carlos Castro',1,4300.00,'2000-03-27',5),(19,'Maria Betânia Amorim',2,5500.00,'2000-03-27',5),(20,'Rita de Castro Campos',2,3800.00,'2000-03-27',5),(21,'Virginia Santos',2,5000.00,'2002-06-21',NULL),(22,'André Alves',NULL,5000.00,'1999-08-25',NULL);
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `project`
--

DROP TABLE IF EXISTS `project`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `project`
--

LOCK TABLES `project` WRITE;
/*!40000 ALTER TABLE `project` DISABLE KEYS */;
INSERT INTO `project` VALUES (1,'Alfa'),(2,'Beta'),(3,'Gama'),(4,'Delta'),(5,'Omega'),(6,'Lambda');
/*!40000 ALTER TABLE `project` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `project_employee`
--

DROP TABLE IF EXISTS `project_employee`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `project_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `init_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_EmpregadoProjeto` (`employee_id`),
  KEY `FK_ProjetoEmpregado` (`project_id`),
  CONSTRAINT `FK_EmpregadoProjeto` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`),
  CONSTRAINT `FK_ProjetoEmpregado` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `project_employee`
--

LOCK TABLES `project_employee` WRITE;
/*!40000 ALTER TABLE `project_employee` DISABLE KEYS */;
INSERT INTO `project_employee` VALUES (31,1,1,'2022-01-01','2022-01-31'),(32,2,1,'2022-03-01','2022-02-28'),(33,3,1,'2022-03-01','2022-03-31'),(34,4,2,'2022-04-01','2022-04-30'),(35,5,2,'2022-05-01','2022-05-31'),(36,6,2,'2022-06-01','2022-06-30'),(37,7,3,'2022-07-01','2022-07-31'),(38,8,3,'2022-08-01','2022-08-31'),(39,9,3,'2022-09-01','2022-09-30'),(40,10,4,'2022-10-01','2022-10-31'),(41,11,4,'2022-11-01','2022-11-30'),(42,12,4,'2022-12-01','2022-12-31'),(43,13,5,'2021-01-01','2021-01-31'),(44,14,5,'2021-02-01','2021-02-28'),(45,15,5,'2021-03-01','2021-03-31');
/*!40000 ALTER TABLE `project_employee` ENABLE KEYS */;
UNLOCK TABLES;

The expected values

"João da Silva" 1
"Victor Oliveira"   1
"Paulo Silva Campos"    1
"Pedro Campos"  1
"José da Silva Oliveira"    1
"João Carlos Pereira"   1
"Gilson Machado"    1
"Vicente da Rosa Silva" 1
"Marlon da Silva"   1
"Silvio Luis Oliveira"  1
"Roberto Carlos Castro" 1

Leave a Comment