From: Peter Brawley
Date: June 28 2006 10:02pm
Subject: Re: Distinct problem
List-Archive: http://lists.mysql.com/mysql/199472
Message-Id: <44A2FC87.9000807@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-44A2FC87775C======="
--=======AVGMAIL-44A2FC87775C=======
Content-Type: multipart/alternative; boundary=------------010801090400030002080803
--------------010801090400030002080803
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Tanner
>I am trying to group my results by the last activity on each row, my
query
>looks like this
>select text, dt, item_id from table
>where ....
>group by item_id
>order by dt DESC
SELECT t1.item_id, t1.dt, t1.text
FROM table AS t1
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
WHERE t2.item_id IS NULL;
PB
-----
Tanner Postert wrote:
> The situation is somewhat hard to describe, so please bare with me:
>
> I am trying to group my results by the last activity on each row, my
> query
> looks like this
>
> select text, dt, item_id from table
> where ....
> group by item_id
> order by dt DESC
>
> here is an example record set.
>
>
> text1,2006-06-28 10:00:00,4
> text2,2006-06-28 10:15:00,4
> text3,2006-06-28 10:30:00,8
> text4,2006-06-28 11:00:00,8
>
> the results from the above query would be:
>
> text1,2006-06-28 10:00:00,4
> text3,2006-06-28 10:30:00,8
>
> my problem is that i want the other item to show up. the item with the
> most
> recent DT. it is doing the grouping before it does the ordering. how do i
> specify that I want to see the most recent info when it does the group?
>
> thanks in advance.
>
> Tanner
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
>
--------------010801090400030002080803
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Tanner
>I am trying to group my results by the last activity on each row,
my query
>looks like this
>select text, dt, item_id from table
>where ....
>group by item_id
>order by dt DESC
SELECT t1.item_id, t1.dt, t1.text
FROM table AS t1
LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt < t2.dt
WHERE t2.item_id IS NULL;
PB
-----
Tanner Postert wrote:
The situation is somewhat hard to describe, so please bare
with me:
I am trying to group my results by the last activity on each row, my
query
looks like this
select text, dt, item_id from table
where ....
group by item_id
order by dt DESC
here is an example record set.
text1,2006-06-28 10:00:00,4
text2,2006-06-28 10:15:00,4
text3,2006-06-28 10:30:00,8
text4,2006-06-28 11:00:00,8
the results from the above query would be:
text1,2006-06-28 10:00:00,4
text3,2006-06-28 10:30:00,8
my problem is that i want the other item to show up. the item with the
most
recent DT. it is doing the grouping before it does the ordering. how do
i
specify that I want to see the most recent info when it does the group?
thanks in advance.
Tanner
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
--------------010801090400030002080803--
--=======AVGMAIL-44A2FC87775C=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
--=======AVGMAIL-44A2FC87775C=======--