MySQL - ORDENAR Y ENUMERAR POR BLOQUES

Estimados,

Solicito de su apoyo para generar en MySQL lo siguiente:

Ordenar aleatoriamente las preguntas y las respuestas…, una vez ordenado enumerar cada respuesta de cada pregunta, lo que quiere decir que con cada cambio de pregunta se reinicia la numeración a 1 para ésa fila.

Anexo el archivo de Excel como ejemplo.
En la primer hoja se muestra lo siguiente:
• Primer bloque(DATA) con los datos a considerar(probablemente haya mas), pero es como se tiene inicialmente.
• En el segundo bloque(NUEVA DATA), muestra cómo debe quedar con el ordenado entre pregunta y respuesta y la columna de numeración(COLX).
En la segunda hoja se muestra la estructura pivot como debe quedar(la cual creo ya tener resuelta en base a otras cosas que ya he armado)

Nota: el resultado será leído por PHP para posteriormente generar un XML.

Saludos.

Roberto Rodriguez.

• DATA.zip(10,3 KB)

Hola no entiendi muy bien lo que quieres hacer, pero si lo que quieres es numerar un subconjunto que obtienes en forma aleatoria podrias hacerlo así, con un procedimiento almacenado

DROP PROCEDURE IF EXISTS `test`.`sp_test_rownum`;
DELIMITER $$
CREATE PROCEDURE `test`.`sp_test_rownum` ()
BEGIN
set @rownum = 0;
select (@rownum := @rownum + 1) as rownum, a.* from (select help_topic_id, name from mysql.help_topic order by rand() limit 0, 10) as a;

END$$

DELIMITER ;

CALL `test`.`sp_test_rownum` ();

hola eyanez, gracias por tu respuesta y tambien gracias por el codigo, ése codigo lo que hace es primero ordenar aleatoriamente y luego enumerar los primeros 10 registros....

lo que necesito es para un examen...., donde tengo:
una columna PREGUNTA..., por cada pregunta hay desde 1 hasta N respuestas....

el requerimiento: enumerar cada una de las respuestas por cada pregunta y ordenar los bloques de preguntas y a la vez las respectivas respuestas:

ejemplo de como debe quedar:
preguntaC - respuestaC - 1
preguntaC - respuestaD - 2
preguntaC - respuestaA - 3
preguntaC - respuestaB - 4

preguntaA - respuestaA - 1
preguntaA - respuestaB - 2
preguntaA - respuestaC - 3
preguntaA - respuestaD - 4

preguntaB - respuestaA - 1

preguntaE - respuestaB - 1
preguntaE - respuestaC - 2
preguntaE - respuestaD - 3
preguntaE - respuestaA - 4

preguntaD - respuestaA - 1

adjunto en el post se encuentra la data....

Ok mira aqui ya te desordena las respuestas pero te mantiene el orden de las preguntas, ahora si lo quieres el ejemplo de arriba solo con MySQL (tienes que hacer una tabla temporal y un cursor), si lo puedes hacer con PHP entonces haz 2 tablas relacionadas "preguntas" y "respuestas" haz un select * from preguntas order by rand(), y luego con un ciclo, haces select * from respuestas where pregunta_id = XX order by rand para obtener cada respuesta desordenada. (claro pregunta_id es el campo de relación)

USE `test`;

DROP TABLE IF EXISTS `test`,`tb_preguntas`;
CREATE TABLE `test`.`tb_preguntas` (
`tipo` ENUM('MULTIPLE','ABIERTA'),
`pregunta` VARCHAR(500),
`respuesta` VARCHAR(500),
INDEX `idx_pregunta` (`pregunta`)
) ENGINE = INNODB CHARSET = LATIN1;





INSERT INTO `test`.`tb_preguntas` VALUES
('MULTIPLE','OBRA DE SOR JUANA INES DE LA CRUZ','AMOR ES LABERINTO'),
('MULTIPLE','OBRA DE SOR JUANA INES DE LA CRUZ','LA DESCONFIANZA DEL DELFIN'),
('MULTIPLE','OBRA DE SOR JUANA INES DE LA CRUZ','CORAZON DE TINTA'),
('MULTIPLE','OBRA DE SOR JUANA INES DE LA CRUZ','NAVEGANTES DE ALTAMAR'),
('MULTIPLE','EN QUE ESTADO DE LA REPUBLICA NACIO PORFIRIO DIAZ','TAMAULIPAS'),
('MULTIPLE','EN QUE ESTADO DE LA REPUBLICA NACIO PORFIRIO DIAZ','GUERRERO'),
('MULTIPLE','EN QUE ESTADO DE LA REPUBLICA NACIO PORFIRIO DIAZ','OAXACA'),
('MULTIPLE','EN QUE ESTADO DE LA REPUBLICA NACIO PORFIRIO DIAZ','BAJA CALIFORNIA'),
('MULTIPLE','CULTURA PREHISPANICA CONOCIDA COMO LOS HABITANTES DE HULE Y QUE SE CARACTERIZABAN POR CONSTRUIR GRANDES CABEZAS MONUMENTALES','AZTECA'),
('MULTIPLE','CULTURA PREHISPANICA CONOCIDA COMO LOS HABITANTES DE HULE Y QUE SE CARACTERIZABAN POR CONSTRUIR GRANDES CABEZAS MONUMENTALES','MAYA'),
('MULTIPLE','CULTURA PREHISPANICA CONOCIDA COMO LOS HABITANTES DE HULE Y QUE SE CARACTERIZABAN POR CONSTRUIR GRANDES CABEZAS MONUMENTALES','OLMECA'),
('MULTIPLE','AUTOR DEL DOCUMENTO CONOCIDO COMO LOS SENTIMIENTOS DE LA NACION','MIGUEL HIDALGO'),
('MULTIPLE','AUTOR DEL DOCUMENTO CONOCIDO COMO LOS SENTIMIENTOS DE LA NACION','BENITO JUAREZ'),
('MULTIPLE','AUTOR DEL DOCUMENTO CONOCIDO COMO LOS SENTIMIENTOS DE LA NACION','IGNACIO ALDAMA'),
('MULTIPLE','AUTOR DEL DOCUMENTO CONOCIDO COMO LOS SENTIMIENTOS DE LA NACION','JOSE MA. MORELOS Y PAVON'),
('MULTIPLE','PERSONAJE DE GRAN RELEVANCIA EN LA LUCHA DE INDEPENDENCIA','BENITO JUAREZ'),
('MULTIPLE','PERSONAJE DE GRAN RELEVANCIA EN LA LUCHA DE INDEPENDENCIA','PORFIRIO DIAZ'),
('MULTIPLE','PERSONAJE DE GRAN RELEVANCIA EN LA LUCHA DE INDEPENDENCIA','JOSE MARIA MORELOS Y PAVON'),
('ABIERTA','EN QUE LUGAR SE UBICA LA ZONA ARQUEOLOGICA DEL TAJIN',''),
('MULTIPLE','PRESIDENTE QUE EXPROPIO EN MEXICO LA INDUSTRIA PETROLERA','MANUEL AVILA CAMACHO'),
('MULTIPLE','PRESIDENTE QUE EXPROPIO EN MEXICO LA INDUSTRIA PETROLERA','LAZARO CARDENAS DEL RIO'),
('MULTIPLE','PRESIDENTE QUE EXPROPIO EN MEXICO LA INDUSTRIA PETROLERA','ADOLFO RUIZ CORTINEZ'),
('MULTIPLE','PRESIDENTE QUE EXPROPIO EN MEXICO LA INDUSTRIA PETROLERA','ERNESTO ZEDILLO PONCE DE LEON'),
('ABIERTA','RIO QUE MARCA LA FRONTERA ENTRE MEXICO Y ESTADOS UNIDOS',''),
('ABIERTA','ESTADO DE LA REPUBLICA MEXICANA CON MAYOR EXTENSION TERRITORIAL',''),
('ABIERTA','APROXIMADAMENTE CUANTOS HABITANTES TIENE MEXICO','');


DROP PROCEDURE IF EXISTS `test`.`sp_test_rownum`;
DELIMITER $$
CREATE PROCEDURE `test`.`sp_test_rownum` ()
BEGIN


set @rownum = 0;
select (@rownum := @rownum + 1) as rownum, a.* from (select * from `test`.`tb_preguntas` order by pregunta, rand()) as a;

END$$

DELIMITER ;

CALL `test`.`sp_test_rownum` ();

hola que tal..., gracias..., ya probé tu codigo, solo que el resultado no es como el requerimiento jejeje, por que me trae todos los datos, no enumera individualmente las respuestas por cada pregunta y solo aparecen desordenadas las respuestas mas no las preguntas....

lo que comentas de "si lo puedes hacer con PHP...", es como lo tengo actualmente (select * from preguntas order by rand(), y luego con un ciclo, aplico select * from respuestas where pregunta_id = XX order by rand), me és funcional, pero la idea es reducir el numero de SELECT que aplica el server para poder mejorar los tiempos de respuesta, ya que si un examen tiene 10 preguntas, aplico 10 select, si tiene 50 preguntas aplico 50 select..., ésto multiplicalo por N usuarios que aplican al mismo tiempo el examen...., al final me impacta en recursos del server.

dado lo anterior surgió la idea de pasarlo a pivot, ello significa que debo colocar en columnas(1, 2, 3, 4)..., en la columna 1 iran todas las respuestas identificadas con el numero 1, en la columna 2 iran todas las respuestas identificadas con el numero 2, en la columna 3 iran todas las respuestas identificadas con el numero 3, .......

me puedes ayudar por favor, de ser posible con el codigo de acuerdo a tu sugerencia (tienes que hacer una tabla temporal y un cursor), de preferencia manehar solo cursor estaría padre para no generar mas espacio en la base de datos no hacer mas uso de memoria?.

Hola Roberto, prueba con esta lógica agregando los campos que deseas.

SET @acumulativo := 0;

SELECT por_cliente.pregunta,
(@acumulativo := @acumulativo + 1) AS acumulativo,
respuesta,
CASE WHEN @acumulativo = tot_respuesta THEN @acumulativo := @acumulativo - tot_respuesta ELSE 0 END AS dato
FROM (SELECT pregunta, Count(1) AS tot_respuesta
FROM tb_preguntas
GROUP BY pregunta) por_respuesta,
tb_preguntas
WHERE tb_preguntas.pregunta = por_respuesta.pregunta
ORDER BY 1, 2

espero te ayude.

saludos.

muchas gracias a ambos..., el codigo de Hugo se ajusta perfecto a lo que necesito, enumerar cada respuesta de cada pregunta..., solo le falta el ordenamiento aleatorio a la respuesta pero que el numero en la columna acumulativo no se mueva de fila...., el ordenamiento aleatorio por pregunta en esta consulta al final ya no fué necesario, por que despues de ésto pasaré a pivot la data generada y allí puedo ordenar aleatoriamente las preguntas....

coloco el codigo con el ajuste del ordenamiento de la data inicial y unas notas.

USE `test`;
SET @acumulativo := 0;
SELECT
por_respuesta.pregunta, <----- en ésta linea reemplacé "por_cliente" por "por_respuesta"
respuesta,
(@acumulativo := @acumulativo + 1) AS acumulativo,
CASE WHEN @acumulativo = tot_respuesta THEN @acumulativo := @acumulativo - tot_respuesta ELSE 0 END AS dato
FROM (SELECT pregunta, Count(1) AS tot_respuesta FROM tb_preguntas GROUP BY pregunta) as por_respuesta,
(select * from tb_preguntas order by rand()) as tb_preguntas2 <----- en ésta linea está el ordenado de las respuestas de manera aleatoria
WHERE tb_preguntas2.pregunta = por_respuesta.pregunta
ORDER BY 1 <----- en ésta linea dejé que solo se acomode por pregunta, pero ésta linea en mi caso no es necesario por que las preguntas se guardan en filas seguidas, en caso contrario si es necesario cuando las preguntas no estan ordenadas grupalmente en la data.

saludos y gracias a los dos.