CODEWITHSUNDEEP
phpmysql
roshkattu
roshkattu

Reputation: 251

How to retrieve only unique entries from mysql table

I am trying to get some fields from the following database: 

CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text,
  `date` datetime DEFAULT NULL,
  `fra` int(11) DEFAULT NULL,
  `til` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;


Fra = sender ID;
til = Receiver ID

That has for example this values:

(1, 'This is a message for Obama', '2012-11-14 01:12:06', 1, 3),
(2, 'Hello John! How are you?', '2012-11-14 01:23:43', 3, 1),
(3, 'Hey Jack', '2012-11-14 01:28:11', 1, 2),
(4, 'How are you son?', '2012-11-14 01:28:15', 1, 2),
(5, 'everything allright for you?', '2012-11-14 01:28:23', 1, 2);


The way I want to get the fields from the database is at follows:
I want to get the unique conversations from one user to another. If user 1 has sent a message to user 3, and user 3 replied to user 1, then I would like only to select the last field into the database; Or if user 1 has sent 3 messages to user 2, then I would like to get only the 3rd message to user 2, from user 1. So that means I should only get 2 rows as result

I have tried:

SELECT DISTINCT * FROM messages WHERE fra='".$_SESSION['userid']."' OR til='".$_SESSION['userid']."' GROUP BY fra,til"


and 

"SELECT DISTINCT(til) AS til FROM messages WHERE fra='".$_SESSION['userid']."' OR til='".$_SESSION['userid']."'  ORDER BY id DESC"

but I can't get the result I want.

Can someone explain me how to do this?

Edit: I want only to build threads of user messages that will show the last message sent between two users (that's the scope of the query)

Upvotes: 1

Views: 174

Answers (4)

Malla
Malla

Reputation: 175

I think you can use group-by also get the values..

select * from messages where ID in (select max(ID) from messages where fra='".$_SESSION['userid']."' OR til='".$_SESSION['userid']."' GROUP BY fra,til)

Upvotes: 2

marvin
marvin

Reputation: 479

Here's the answer/example for the sql statement: http://sqlfiddle.com/#!2/ae56b/10

select * 
from messages m1
where m1.date = (select max(m2.date) 
    from messages m2 
    where 
    case when m2.fra < m2.til then m2.fra else m2.til end = 
    case when m1.fra < m1.til then m1.fra else m1.til end
    and 
    case when m2.fra > m2.til then m2.fra else m2.til end = 
    case when m1.fra > m1.til then m1.fra else m1.til end
);

Upvotes: 2

artragis
artragis

Reputation: 3713

I think you can use sort of "key=>value" collection to emulate it

your query can be 

SELECT @discution := (MAX(til,Fra)+","+MIN(til,Fra)) AS key , messages.* FROM messages
 ORDER BY id DESC LIMIT 1 OFFSET 0 GROUP BY @discution

Upvotes: 0

Gr&#252;se
Gr&#252;se

Reputation: 1175

This is not really a case of DISTINCT, it's more a case where you want to limit your result to 1 record. Try this:

SELECT * FROM messages WHERE fra='".$_SESSION['userid']."' OR til='".$_SESSION['userid']."' ORDER BY `date` DESC LIMIT 1

Upvotes: 1

Related Questions