On 3/14/07, Bill Guion <[EMAIL PROTECTED]> wrote:
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.
thx bill. yep. the DISTINCT is applied to the query first and then the
ORDER BY hence it takes the 1st 2 rows and then applies the ORDER BY.
however, in the application i need to get the rows ORDER BY ts DESC
first and then remove duplicates.
so it'd take the 4 rows and order them by ts desc
------------
page_id
------------
1
2
2
1
------------
and then remove duplicates to give
------------
page_id
------------
1
2
------------
any query to get the above desired result ?
thx.
yashesh bhatia
-----===== 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]
--
----------------------------------------------------------------
Go Pre
http://www2.localaccess.com/rlalonde/pre.htm
----------------------------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
