Re: using DISTINCT after the ORDER BY clause has been applied
On 3/14/07, Bill Guion [EMAIL PROTECTED] wrote: At 11:33 PM + 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_idint unsignedpage id of the page viewed user_id int unsigneduser id of the page viewed ts timestamptimestamp 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]
Re: using DISTINCT after the ORDER BY clause has been applied
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 + 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_idint unsignedpage id of the page viewed user_id int unsigneduser id of the page viewed ts timestamptimestamp 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]
using DISTINCT after the ORDER BY clause has been applied
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 SELECT DISTINCT page_id FROM page_viewed WHERE user_id =1 ORDER BY ts DESC 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]
using DISTINCT after the ORDER BY clause has been applied
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_idint unsignedpage id of the page viewed user_id int unsigneduser id of the page viewed ts timestamptimestamp 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using DISTINCT after the ORDER BY clause has been applied
At 11:33 PM + 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_idint unsignedpage id of the page viewed user_id int unsigneduser id of the page viewed ts timestamptimestamp 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]
