List:General Discussion« Previous MessageNext Message »
From:Yashesh Bhatia Date:March 13 2007 3:26pm
Subject:using DISTINCT after the ORDER BY clause has been applied
View as plain text  
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.

----------------------------------------------------------------
Go Pre
http://www2.localaccess.com/rlalonde/pre.htm
----------------------------------------------------------------
Thread
using DISTINCT after the ORDER BY clause has been appliedYashesh Bhatia13 Mar