0

I would like to select the most recent record for every group, but I have problems because I got an error

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'query1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by```

I have two joined tables: users and messages. So, users can send messages to each other, and I would like to select for given user the last message from his contacts.

users enter image description here

messages enter image description here

I managed to find all contacts for specific user using CASE WHEN:

SELECT * FROM
 (
 SELECT *,
     CASE
        WHEN idFrom = 18 THEN idTo
        WHEN idTo = 18 THEN idFrom
        ELSE ''
    END AS contact
 FROM messages 
 WHERE idFrom = 18 OR idTo = 18
 ) query1
INNER JOIN users ON users.user_id = query1.contact

enter image description here

but I don't know how to group these contacts.

Expected result is:

enter image description here

I prepared a data set...

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` 
(`user_id`, `firstName`, `lastName`) 
VALUES
(18, 'Bart', 'Simpson'),
(33, 'Marge', 'Simpson'),
(45, 'Homer', 'Simpson'),
(99, 'Lisa', 'Simpson');

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `idFrom` int(11) DEFAULT NULL,
  `idTo` int(11) DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `messageDate` datetime NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;
COMMIT;


INSERT INTO messages
 (idFrom, idTo, message, messageDate)
 VALUES 
 (18, 33, 'Hello Marge', '2000-05-05 16:29:25'),
 (33, 18, 'Hello Bart', '2001-05-05 16:29:25'),
 (18, 99, 'Hello Lisa', '2002-05-05 16:29:25'),
 (99, 45, 'Hello Homer', '2003-05-05 16:29:25'),
 (18, 99, 'Hello Lisa Again!!!', '2004-05-05 16:29:25'),
 (33, 18, 'Hello Bart Again', '2005-05-05 16:29:25');

select version();

...and Fiddle of same

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Sap1234
  • 147
  • 7
  • 1
    There are many answers posted in the [tag:greatest-n-per-group] tag. – Bill Karwin May 05 '21 at 16:32
  • Thank you for prompt reply, but to be honest, I don't know how to implement it. As I said, I got stuck, and when I try group it I got an error. Maybe, it is because I used CESE WHEN to find contact from two columns. – Sap1234 May 05 '21 at 16:32
  • please take a look why not to upoliad a picture of data or code https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question – nbk May 05 '21 at 16:37
  • A clear, concise, and well-formatted question... that's been asked and answered a billion times already (and sometimes correctly) – Strawberry May 05 '21 at 16:40
  • This is not true. – Sap1234 May 05 '21 at 16:44
  • your fiddle link indicates you are on mysql 8.0; is that true? If so, look at the row_number answer in the indicated duplicate question: https://stackoverflow.com/a/34212496/17389 Edit your question if you have trouble with that, showing exactly what you tried and what happened. – ysth May 05 '21 at 16:50
  • Well isn't obvious from my question what I tried? I provided Fiddle with my best attempt. The problem is when I try to group records I got the error that I also described. So, I don't have a clue how to proceed. – Sap1234 May 05 '21 at 16:56
  • here is a solution for you https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ff98f12e73b9c3f808a957ff8a16835c – nbk May 05 '21 at 17:04
  • @nbk Thank you very much. – Sap1234 May 05 '21 at 17:17

0 Answers0