Hey guys,
I have worked out the SQL query I would like to implement but am currently
struggling to create the Django query. I have been attempting to use the
.aggregate() & Max() methods but cant seem to get the right result. Any
help is much appropriated.
SELECT temp.*
FROM item_detail temp
INNER JOIN
(SELECT item, MAX(created_date) AS MaxDateTime, MAX(id) AS MaxID
FROM item_detail
WHERE approved = true
GROUP BY item) grouped_join
ON temp.item = grouped_join.item
AND temp.created_date = grouped_join.MaxDateTime
AND temp.id = grouped_join.MaxID
CREATE TABLE item_detail
(
id INT,
item INT,
created_date TIMESTAMP,
description VARCHAR(10),
approved BOOLEAN
);
INSERT INTO item_detail VALUES(1, 1, '2009-04-03', 'dk', true);
INSERT INTO item_detail VALUES(2, 1, '2009-04-03', 'dk mama', false);
INSERT INTO item_detail VALUES(3, 1, '2009-03-04', 'dk 2', true);
INSERT INTO item_detail VALUES(4, 2, '2009-03-04', 'botw', true);
INSERT INTO item_detail VALUES(5, 2, '2009-04-04', 'botw 2', true);
INSERT INTO item_detail VALUES(6, 3, '2009-03-05', 'cod mw', true);
INSERT INTO item_detail VALUES(7, 4, '2008-12-25', 'bf 4', true);
INSERT INTO item_detail VALUES(8, 4, '2009-01-05', 'bf mw', false);
INSERT INTO item_detail VALUES(9, 2, '2009-04-06', 'botw 4', false);
INSERT INTO item_detail VALUES(10, 3, '2009-04-06', 'cod newy', false);
INSERT INTO item_detail VALUES(12, 1, '2009-04-07', 'dk ea', false);
INSERT INTO item_detail VALUES(13, 1, '2009-05-08', 'dk 3', true);
INSERT INTO item_detail VALUES(14, 1, '2009-05-08', 'dk 3-3', true);
Here is the link to the SQL Fiffle: http://www.sqlfiddle.com/#!17/260b62/1
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/88f11149-eb65-4d0b-ad6e-729d9db6c6d2%40googlegroups.com.
