Select DISTINCT query - HELP

[hermanSA]

Hi,

I need to retrieve only 1 row for each 'message_id', with the Latest Date('sent_date'):

-------------------------------------
message_id | sent_date |
-------------------------------------
805 | 2010-08-04 17:48:24 | <---
805 | 2010-08-03 17:48:24 |
1007 | 2010-08-07 17:48:24 |
1007 | 2010-08-09 17:48:24 | <---
1007 | 2010-08-08 17:48:24 |
-------------------------------------

The Results should be:

-------------------------------------
message_id | sent_date |
-------------------------------------
805 | 2010-08-04 17:48:24 |
1007 | 2010-08-09 17:48:24 |
-------------------------------------

I have tried the following query which only retrieves the latest date, and not the latest date for each 'message_id'..

$sql = "SELECT DISTINCT message_id, max(date_sent)
	FROM mail";

also tried:

$sql = "SELECT DISTINCT message_id, date_sent
	FROM mail
          HAVING date_sent = max(date_sent)";

Please help

[MeSampath]

Hi,

You need to use "Group by" functionality in SQL.

Here is the modified query;

SELECT DISTINCT message_id, max(date_sent)
	FROM mail group by message_id

Thank you.

[hermanSA]

Thanks a lot. That worked perfectly :)

[hermanSA]

hi

[hermanSA]

@MeSampath,

hi, your solution seemed to have worked but actually gave me some undesired results.
I'll explain in more depth:

The query:

SELECT DISTINCT mess_id, id, message, MAX(date_sent) as date_sent
    FROM mail
    GROUP BY message_id

should have retrieved the two rows in Bold below..

---------------------------------------------------------
 id |   mess_id    |   message    |      date_sent      |
---------------------------------------------------------
 <strong>0</strong>  |  <strong>12</strong>          |  <strong>mess 12_0</strong>   | <strong>2010-08-04 21:00:22</strong> |
 1  |  12          |  mess 12_1   | 2010-08-03 17:30:55 |
 2  |  448         |  mess 448_0  | 2010-07-11 21:12:20 |
 3  |  448         |  mess 448_1  | 2010-07-12 17:30:48 |
 <strong>4</strong>  |  <strong>448</strong>         |  <strong>mess 448_2</strong>  | <strong>2010-07-13 17:48:24</strong> |
---------------------------------------------------------

but retrieved the rows in Bold below:

---------------------------------------------------------
 id |   mess_id    |   message    |      date_sent      |
---------------------------------------------------------
 <strong>0</strong>  |  <strong>12</strong>          |  <strong>mess 12_0</strong>   | <strong>2010-08-04 21:00:22</strong> |
 1  |  12          |  mess 12_1   | 2010-08-03 17:30:55 |
 2  |  448         |  mess 448_0  | 2010-07-11 21:12:20 |
 <strong>3</strong>  |  <strong>448</strong>         |  <strong>mess 448_1</strong>  | 2010-07-12 17:30:48 |
 4  |  448         |  mess 448_2  | <strong>2010-07-13 17:48:24</strong> |
---------------------------------------------------------

so the first row is fine,
but the second row chose a distinct 'mess_id' and MAX(sent_date).. but not from the same 'row'..

any input appreciated!

[tesuji]

Such wrong result is quite usual, it's a painful mysql gotcha!

In your query

SELECT DISTINCT mess_id, id, message, MAX(date_sent) as date_sent FROM mail GROUP BY message_id

ALL columns not being parameters of aggregate functions (max(), sum(), count() etc.) must always be enumerated in GROUP BY clause. There is no exception from this stringent rule. All but one database systems refuse execution of queries contravening this rule.

So your correct query is:

SELECT DISTINCT mess_id, id, message, MAX(date_sent) as date_sent FROM mail GROUP BY message_id, id, message

Note: If message contains widely unique values, there would be nothing to group by. Possibly you need to drop message from query (I guess, you don't like that). GROUP BY clause acts this way: It sorts out all row where (message_id, id, message) have identically same values. From this subset max() will be taken. The result will be listed. If any of these three columns changes its value, sort out starts etc. In extreme case if for example mess_id is primary key of your table, no grouping will happen and all rows be listed.

You may read what I wrote there , especially about chapter 11.16.3 of MySQL reference manual.

-- tesu

[hermanSA]

Thanks tesu,

I read your other post as well and the examples you gave and i have a much better understanding for the future, but;

SELECT DISTINCT mess_id, id, message, MAX(date_sent) as date_sent FROM mail GROUP BY message_id, id, message

..retrievesAll the rows correctly, but i only want 2 rows.(unique mess_id with latest sent_date)

so im getting the same results as:

SELECT message_id, message, date_sent as date_sent FROM mail";

have i done something wrong?

[tesuji]

>>> ..retrieves All the rows correctly ..

that means that mess_id or id are unique keys. In such case mysql cannot do any mischief for all rows will be selected. (no grouping).

-- tesu

[tesuji]

From your example

---------------------------------------------------------
 id |   mess_id    |   message    |      date_sent      |
---------------------------------------------------------
 0  |  12          |  mess 12_0   | 2010-08-04 21:00:22 |
 1  |  12          |  mess 12_1   | 2010-08-03 17:30:55 |
 2  |  448         |  mess 448_0  | 2010-07-11 21:12:20 |
 3  |  448         |  mess 448_1  | 2010-07-12 17:30:48 |
 4  |  448         |  mess 448_2  | 2010-07-13 17:48:24 |
---------------------------------------------------------

you would like to select both green-colored rows you said so. Can you tell me what are things in common of these rows?

-- tesu

[tesuji]

you may try this:

select mess_id, max(date_sent) as max_date_sent from yourtable group by mess_id order by mess_id, max_date_sent;

-- tesu

[hermanSA]

thanks tesu,

I'll get back to you tomorrow, it's midnight and my brain is fried.

[tesuji]

Hello and good morning

I was playing about with your sample data and I found a simple solution based on self-join. I'll explain it in two steps:

/* 1. Step: All different mess_id and their corresponding maximum date_sent-timestamps are selected:*/

select mess_id, max(date_sent) as maxdate from yourtable group by mess_id;

/*
The result should be:

---------------------------------------
|   mess_id    |       maxdate        |
---------------------------------------
|  12          |  2010-08-04 21:00:22 |
|  448         |  2010-07-13 17:48:24 |
---------------------------------------

To get the missing columns id and message this intermediate result must be self-joined with original table. So the 1st-step select is put in from clause of a second select and joined with original table. Thanks God, such kind of subselect in from-clause has finally been supported by mysql since near version 4.

2. Step: Self-join
*/
select y.id as "ID", y.mess_id as "Message id", y.message as "Message", y.date_sent as "Date sent"
from (select mess_id, max(date_sent) as maxdate from yourtable group by mess_id) as x 
  join yourtable y on x.mess_id = y.mess_id and x.maxdate = y.date_sent order by y.id;

/*
Now the result should be:

---------------------------------------------------------
 ID |  Message id  |   Message    |      Date sent      |
---------------------------------------------------------
 0  |  12          |  mess 12_0   | 2010-08-04 21:00:22 |
 4  |  448         |  mess 448_2  | 2010-07-13 17:48:24 |
---------------------------------------------------------
*/

Simple solution, isn't it :-O

You may test the select statement of 2nd step. There might be typos yet it should fit mysql syntactically largely. I didn't test it so far.

-- tesu

[hermanSA]

Super!

That worked 100%

Thanks So much tesu