[Conny Westh]

-- Med följande create statements:

delimiter $$

CREATE TABLE `instruments` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `instrument_name` varchar(45) NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$


delimiter $$

CREATE TABLE `trade_data` (
  `item_id` int(11) NOT NULL,
  `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Value` int(11) NOT NULL,
  PRIMARY KEY (`item_id`,`datetime`),
  KEY `trade_data_instrumentsItem_id` (`item_id`),
  CONSTRAINT `trade_data_instrumentsItem_id` 
    FOREIGN KEY (`item_id`) 
    REFERENCES `instruments` (`item_id`) 
    ON DELETE NO ACTION 
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

-- Testdata:

INSERT INTO Instruments(Instrument_name) VALUES ('Volvo A');
INSERT INTO Instruments(Instrument_name) VALUES ('Volvo B');

INSERT INTO trade_data(item_id,datetime,value) VALUES (1, '2007-03-03 20:20:20'  ,5);
INSERT INTO trade_data(item_id,datetime,value) VALUES (1, '2007-03-03 20:20:25'  ,4);
INSERT INTO trade_data(item_id,datetime,value) VALUES (1, '2007-03-03 20:20:30'  ,5);
INSERT INTO trade_data(item_id,datetime,value) VALUES (2, '2007-03-14 18:34:45' ,1);
INSERT INTO trade_data(item_id,datetime,value) VALUES (1, '2007-03-10 17:15:30'  ,1);
INSERT INTO trade_data(item_id,datetime,value) VALUES (1, '2007-03-10 17:15:45'  ,7);
INSERT INTO trade_data(item_id,datetime,value) VALUES (3, '2007-03-15 18:34:45'  ,1);

-- Då levererar denna kod det önskade resultatet - Glöm inte order by på slutet
SELECT t1 . * , instruments . *
FROM (
SELECT *
FROM trade_data
ORDER BY `datetime` DESC
) AS t1
LEFT JOIN instruments ON t1.item_id = instruments.item_id
GROUP BY t1.item_id
ORDER BY Value;

-- Det funkar även att skriva så här för att få samma resultat (utan GROUP BY)
SELECT td1.*
FROM trade_data td1
WHERE td1.datetime = (select max(td2.datetime) from trade_data td2 WHERE td2.item_id=td1.item_id)
ORDER BY td1.Value;

-- Lägg upp en view så blir det enkalt att anropa vid behov
CREATE VIEW `test`.`trade_view` AS
SELECT td1.*
FROM trade_data td1
WHERE td1.datetime = (select max(td2.datetime) from trade_data td2 WHERE td2.item_id=td1.item_id)
ORDER BY td1.Value;

-- Användning av vyn:
SELECT * FROM trade_view;