At 11:33 PM +0000 3/13/07, [EMAIL PROTECTED] wrote:
Date: Tue, 13 Mar 2007 20:56:08 +0530
To: mysql@lists.mysql.com
From: "Yashesh Bhatia" <[EMAIL PROTECTED]>
Subject: using DISTINCT after the ORDER BY clause has been applied
Message-ID: <[EMAIL PROTECTED]>
Hello:
I had a quick question on using the DISTINCT clause in a SELECT query.
I have the following table which stores webpages viewed
table: page_viewed
page_id int unsigned "page id of the page viewed"
user_id int unsigned "user id of the page viewed"
ts timestamp "timestamp of the page view".
Now i need to query the most recently viewed distinct pages and i have
the following data
----------------------------------------------------------------------------
page_id user_id ts
----------------------------------------------------------------------------
1 1 2007-03-13 20:40:46
2 1 2007-03-13 20:40:53
2 1 2007-03-13 20:41:01
1 1 2007-03-13 20:41:10
----------------------------------------------------------------------------
so basically i tried to write a query for recently viewed (for user_id
1) as follows
SELECT DISTINCT page_id
FROM page_viewed
WHERE user_id =1
ORDER BY ts DESC
however, this does not give me the result as i needed, i'd like to have it as
------------
page_id
------------
1
2
------------
but the output is
------------
page_id
------------
2
1
------------
therefore the DISTINCT clause would be first used to filter the rows
and then the ORDER BY would be applied, is there anyway to specify
that DISTINCT be applied after the ORDER BY clause ? if not, any other
way i could retrieve the above data ?
Thanks.
Yashesh Bhatia.
It looks to me as if your query returned exactly what you asked for.
It found the first two rows (other rows are not distinct), and then
ordered them in descending order by time stamp. Descending is largest
to smallest. TS for row 2 is larger than TS for row 1.
-----===== Bill =====-----
--
You were born with all you need to win at life.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]