[mephisto73]

Har letat ett tag nu men kan inte komma på hur man kan hämta DISTINCT eller GROUP BY och samtidigt sortera på ett annat fält.

Exempel:

T1
item_id | datetime | value
1 | 2007-03-03 20:20:20 | 5
1 | 2007-03-03 20:20:25 | 4
1 | 2007-03-03 20:20:30 | 5
2 | 2007-03-14 18:34:45 | 1
1 | 2007-03-10 17:15:30 | 1
1 | 2007-03-10 17:15:45 | 7
2 | 2007-03-15 18:34:45 | 1

Alltså, hur kan jag hämta de rader som innehåller de nyaste värderna (högst datetime) för varje item_id?

Jag vill alltså ha ut 2 rader i detta exempel, nämligen

item_id | datetime | value
2 | 2007-03-15 18:34:45 | 1
1 | 2007-03-10 17:15:45 | 7

Mina försök med DISTINCT och GROUP BY resulterar i att de första raderna för varje item_id returneras, oavsett vad jag sorterar på...

Tacksam för hjälp.

[Clarence]

Du är tvungen att använda en subquery där du gör din sort först.

[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;

[mephisto73]

Ok, för framtida referens:

SELECT t1 . * , instruments . *
FROM (
SELECT *
FROM trade_data
ORDER BY `timestamp` DESC
) AS t1
LEFT JOIN instruments ON t1.instrument_id = instruments.ID
GROUP BY t1.instrument_id

Denna fråga returnerar de senaste raderna för varje unik instrument_id.

[Conny Westh]

Subselect i FROM Clausen ställer tydlugen till problem när man skapar en View i MySQL 5.1.53.

[MRDJ]

Ursprungligen postat av ConnyWesth

Subselect i FROM Clausen ställer tydlugen till problem när man skapar en View i MySQL 5.1.53.

I allmänhet bör man undvika subquerys, speciellt i mysql då det drar ganska mycket prestanda.
Det går oftast att lösa på andra sätt.