Distinct problem 2006-06-28 - By Peter Brawley
Back
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
>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859 (See http://ISO-8859.ora-code.com)-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tanner<br>
<br>
>I am trying to group my results by the last activity on each row,
my query
<br>
>looks like this
<br>
>select text, dt, item_id from table
<br>
>where ....
<br>
>group by item_id
<br>
>order by dt DESC <br>
<span>
<pre>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;</pre>
</span>PB<br>
<br>
-- --<br>
<br>
<br>
Tanner Postert wrote:
<blockquote
cite="mid55e487640606281427u22bfb26bo641495fdfe812e03@(protected)"
type="cite">The situation is somewhat hard to describe, so please bare
with me:
<br>
<br>
I am trying to group my results by the last activity on each row, my
query
<br>
looks like this
<br>
<br>
select text, dt, item_id from table
<br>
where ....
<br>
group by item_id
<br>
order by dt DESC
<br>
<br>
here is an example record set.
<br>
<br>
<br>
text1,2006-06-28 10:00:00,4
<br>
text2,2006-06-28 10:15:00,4
<br>
text3,2006-06-28 10:30:00,8
<br>
text4,2006-06-28 11:00:00,8
<br>
<br>
the results from the above query would be:
<br>
<br>
text1,2006-06-28 10:00:00,4
<br>
text3,2006-06-28 10:30:00,8
<br>
<br>
my problem is that i want the other item to show up. the item with the
most
<br>
recent DT. it is doing the grouping before it does the ordering. how do
i
<br>
specify that I want to see the most recent info when it does the group?
<br>
<br>
thanks in advance.
<br>
<br>
Tanner
<br>
<br>
<pre wrap="">
<hr size="4" width="90%">
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
</pre>
</blockquote>
</body>
</html>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
