hi bill..
thx a lot for that tip. it worked fine.. this is the final one
SELECT DISTINCT t1.page_id
FROM (
SELECT page_id
FROM page_viewed
ORDER BY ts DESC
)t1
thanks.
yashesh bhatia.
On 3/14/07, Bill Guion <[EMAIL PROTECTED]> wrote:
At 8:48 PM +0530 3/14/07, Yashesh Bhatia wrote:
>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
How about some variation of:
SELECT DISTICNT page_id from
(SELECT page_id, user_id, ts
FROM page_viewed
ORDER BY ts DESC);
Some how you will have to sort the input to the SELECT DISTINCT first.
-----===== Bill =====-----
>> >
>>>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
>----------------------------------------------------------------
--
Read on a lawyer's tombstone: "Motion denied".
--
----------------------------------------------------------------
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]
