0

I have a query SQL Fiddle which contains 2 tables

tbl_data

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;

tbl_data

tbl_slot_booking

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;

tbl_slot_booking

Requirement:

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;

enter image description here

Issue:

  1. The output is showing duplicate result if I am not using group by clause.
  2. If I use group by clause, the oldest date is retrieved from the records.

EDIT

Although I am able to successfully sort the content by booking time field_data.created using MAX(FROM_UNIXTIME(field_data.created)), however the other fields field_data.id, field_data.title are still showing oldest result in each row.

  • You probably need to JOIN on a subquery with MAX(yourTime), as it is mysql returns indeterminate time. – Mihai Sep 5 '14 at 18:21
  • It is rather difficult to understand your model since it does not contain keys, but in general you need to use a full group by (all attributes in the select that is not part of an aggregate function such as max). Otherwise you will get a random result. – Lennart Sep 6 '14 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.