SQL and/or JPQL Statement to retrieve single record sorted by date desc

Question

Require SQL and/or JPQL statement to retrieve latest record for a given account sorted by date (descending). It should return a single record (latest record) for all users e.g. if 'user a' sends/receives multiple message from 'user b', 'user c', and 'user d', it would return the latest record for each user (3 in total).

My Attempt at JPQL:

SELECT DISTINCT dm FROM DirectMessage dm WHERE (dm.messageFrom.id=1 OR dm.messageTo.id=1)  GROUP BY dm.id, dm.messageFrom.id, dm.messageTo.id ORDER BY dm.date DESC

Concrete Example with schema:

if I wished to return the latest record for 'user a' then correct number of record returned would be 3 (insert statement for each of these 3 records below):

1.INSERT INTO direct_message VALUE(2, 2, 1, 'hello back', now());
2.INSERT INTO direct_message VALUE(4, 3, 1, 'hello back', now());
3.INSERT INTO direct_message VALUE(6, 4, 1, 'hello back', now());

Script

CREATE TABLE account(
    id bigint UNSIGNED NOT NULL auto_increment,
    username varchar(75) NOT NULL,
    name varchar(70) NOT NULL,
    date datetime NOT NULL,
    CONSTRAINT pkey_account_id PRIMARY KEY(id)
);

INSERT INTO account VALUES (1,'user_a','User A', now());
INSERT INTO account VALUES (2,'user_b','User B', now());
INSERT INTO account VALUES (3,'user_c','User C', now());
INSERT INTO account VALUES (4,'user_d','User D', now());



    CREATE TABLE direct_message(
        id bigint UNSIGNED NOT NULL auto_increment,
        message_from_id bigint UNSIGNED NOT NULL,
        message_to_id bigint UNSIGNED NOT NULL,
        message_text varchar(200) NOT NULL,
        date datetime NOT NULL,
        CONSTRAINT pkey_dm_id PRIMARY KEY(id),
        CONSTRAINT fk_direct_message_to FOREIGN KEY (message_to_id) REFERENCES account(id),
        CONSTRAINT fk_message_from FOREIGN KEY (message_from_id) REFERENCES account(id)
    );

    INSERT INTO direct_message VALUE(1, 1, 2, 'hello', now());
    INSERT INTO direct_message VALUE(2, 2, 1, 'hello back', now());
    INSERT INTO direct_message VALUE(3, 1, 3, 'hello', now());
    INSERT INTO direct_message VALUE(4, 3, 1, 'hello back', now());
    INSERT INTO direct_message VALUE(5, 1, 4, 'hello', now());
    INSERT INTO direct_message VALUE(6, 4, 1, 'hello back', now());
    INSERT INTO direct_message VALUE(7, 2, 4, 'hello', now());
    INSERT INTO direct_message VALUE(8, 4, 2, 'hello back', now());

Answer 1

I think you want:

select dm.*
from direct_message dm
where @userid in (dm.message_from_id, dm.message_to_id) and
      dm.date = (select max(dm2.date)
                 from direct_message dm2
                 where (dm2.message_from_id, dm2.message_to_id) in ( (dm.message_from_id, dm.message_to_id), (dm.message_to_id, dm.message_from_id) )
                );

EDIT:

It seems strange to have different messages with exactly the same date/time stamp, but you can distinguish them using the id:

select dm.*
from direct_message dm
where 1 in (dm.message_from_id, dm.message_to_id) and
      dm.id = (select dm2.id
               from direct_message dm2
               where (dm2.message_from_id, dm2.message_to_id) in ( (dm.message_from_id, dm.message_to_id), (dm.message_to_id, dm.message_from_id) )
               order by dm2.date desc
               limit 1
              );