MySQL Forums
Forum List  »  Spanish

[QUERY] obtener valores "no agregados" en GROUP BY
Posted by: Marco Rios
Date: February 02, 2017 03:24PM

Que tal,
he estado buscando una solución para el siguiente problema:
necesito obtener el id (id_tarifa) de la tarifa mas baja para cada fecha, dentro de un período dado de tiempo.

Para generar la tabla de ejemplo:
DROP TABLE IF EXISTS tarifas;
CREATE TABLE tarifas (
  id_tarifa int(11) NOT NULL AUTO_INCREMENT,
  id_contrato int(11) NOT NULL DEFAULT '0',
  fecha date NOT NULL DEFAULT '1980-01-01',
  tarifa decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (id_tarifa)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

INSERT INTO `tarifas` 
	VALUES 
	(2251754,22576,'2017-04-10',45.00),
        (2251755,22576,'2017-04-11',45.00),
        (2251756,22576,'2017-04-12',45.00),
        (2251757,22576,'2017-04-13',45.00),
        (2251758,22576,'2017-04-14',45.00),
        (2251759,22576,'2017-04-15',45.00),
        (2251760,22576,'2017-04-16',45.00),
        (2251945,22580,'2017-04-17',42.00),
        (2251946,22580,'2017-04-18',42.00),
        (2251947,22580,'2017-04-19',42.00),
        (2251948,22580,'2017-04-20',42.00),
        (2252119,22583,'2017-04-10',50.00),
        (2252120,22583,'2017-04-11',50.00),
        (2252121,22583,'2017-04-12',50.00),
        (2252122,22583,'2017-04-13',50.00),
        (2252123,22583,'2017-04-14',50.00),
        (2252124,22583,'2017-04-15',50.00),
        (2252125,22583,'2017-04-16',50.00),
        (2252310,22587,'2017-04-17',47.00),
        (2252311,22587,'2017-04-18',47.00),
        (2252312,22587,'2017-04-19',47.00),
        (2252313,22587,'2017-04-20',47.00),
        (2252484,22590,'2017-04-10',55.00),
        (2252485,22590,'2017-04-11',55.00),
        (2252486,22590,'2017-04-12',55.00),
        (2252487,22590,'2017-04-13',55.00),
        (2252488,22590,'2017-04-14',55.00),
        (2252489,22590,'2017-04-15',55.00),
        (2252490,22590,'2017-04-16',55.00),
        (2252675,22594,'2017-04-17',52.00),
        (2252676,22594,'2017-04-18',52.00),
        (2252677,22594,'2017-04-19',52.00),
        (2252678,22594,'2017-04-20',52.00);

Con la siguiente consulta se obtiene la tarifa mas baja para cada fecha, pero no se identifica a que id_tarifa corresponde cada una

SELECT fecha, MIN(tarifa)
	FROM tarifas
	WHERE fecha BETWEEN '2017-04-15' AND '2017-04-18'
	GROUP BY fecha;

¿Cómo puedo hacer para obtener esos id_tarifa?

Thanks in advance for your valuable help.

Acer Aspire 2920
CPU: Intel Core 2 Duo T7300@2.00GHzX2
RAM: 4GB
Graphics: Intel 965GM
OS: UBUNTU 16.04 LTS 64bits
--
Best regards
Marco
Mexico City, Mexico

Options: ReplyQuote


Subject
Views
Written By
Posted
[QUERY] obtener valores "no agregados" en GROUP BY
359
February 02, 2017 03:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.