I have a query SQL Fiddle which contains 2 tables
CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT '', uid int(11) NOT NULL DEFAULT '0', status int(11) NOT NULL DEFAULT '1', created int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS tbl_slot_booking ( bundle varchar(128) NOT NULL DEFAULT '', entity_id int(10) unsigned NOT NULL, field_slot_target_id int(10) unsigned ) ENGINE=InnoDB;
I want to list all the distinct slots sorted by latest booking added. Currently I am able to show the results as follows
Following is the query and result output
SELECT td.id AS booking_id, td.title AS booking_title, node_slot.id AS slot_id, node_slot.title AS slot_title, FROM_UNIXTIME(td.created) AS booking_time FROM tbl_data td LEFT JOIN tbl_slot_booking tsb ON td.id = tsb.entity_id LEFT JOIN tbl_data node_slot ON tsb.field_slot_target_id = node_slot.id WHERE td.type = 'slot_booking' #GROUP BY (field_slot_target_id) ORDER BY td.created DESC;
- The output is showing duplicate result if I am not using group by clause.
- If I use group by clause, the oldest date is retrieved from the records.
Although I am able to successfully sort the content by booking time
MAX(FROM_UNIXTIME(field_data.created)), however the other fields
field_data.id, field_data.title are still showing oldest result in each row.