Eliminating duplicates from self join results
Hello: I have the following table select * from addressbook +++ | id | email | +++ | 1 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | | 3 | [EMAIL PROTECTED] | +++ 3 rows in set (0.00 sec) Now i wanted to find a list of duplicate contacts wherein i can get the 1st contact with same email and merge with the others. so in the above case id 1 has duplicates 2 and 3, 2 has 1 3 and 3 and 1 2. I'm only interested in getting the first set of duplicates i.e. 1 has duplicates 2 3. So i tried the query select t1.id as id1, t2.id as id2, t1.email as email1, t2.email as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id +-+-+++ | id1 | id2 | email1 | email2 | +-+-+++ | 1 | 2 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 1 | 3 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 2 | 1 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 2 | 3 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 3 | 1 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | | 3 | 2 | [EMAIL PROTECTED] | [EMAIL PROTECTED] | +-+-+++ then i tried the query similar to the one suggested in the MySQL Cookbook Recipe 14.5 select DISTINCT if(t1.id t2.id, t1.id, t2.id) as id1, if(t1.id t2.id, t1.email, t2.email) as email1, if(t1.id t2.id, t2.id, t1.id) as id2, if(t1.id t2.id, t2.email, t1.email) as email2 from addressbook t1, addressbook t2 where t1.email = t2.email and t1.id != t2.id order by t1.id +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | | 2 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ I'm stuck trying to get a query that will give me only +-++-++ | id1 | email1 | id2 | email2 | +-++-++ | 1 | [EMAIL PROTECTED] | 2 | [EMAIL PROTECTED] | | 1 | [EMAIL PROTECTED] | 3 | [EMAIL PROTECTED] | +-++-++ Any help, feeback is deeply appreciated. Thanks a bunch in advance. Yashesh Bhatia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB Storage question
hello: i have the following table in the database test_transaction -- -- Table structure for table `test_blob` -- CREATE TABLE `test_blob` ( `f1` blob NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; i saw the files in the mysql var directory $ ls -l test_transaction/test_blob.frm -rw-rw 1 mysql mysql 8556 May 12 20:04 test_transaction/test_blob.frm so this is an approx 8k file. now i added 2 rows each with binary files of size 8k but when i list test_blob.frm the size remains unchanged (still 8556 bytes). Where are the blob datatypes stored ? i did a bit of reading in the manual it mentions they are stored in hidden tables ? if so, is it possible to see these hidden tables and the entries / sizes of rows in them ? 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]
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]
Re: A tricky Query
hi. can you give more details ? how is effective_price for today calculated - for example if u need price for 11th oct 2006, i'm assuming all u need is the price for date_from / date_to when there's a match in the range. yashesh bhatia. On 10/7/06, abhishek jain [EMAIL PROTECTED] wrote: Hi, I have a table like : ID date_from date_to price_code price dateadded 1 07:10:2006 31:12:2099 p11007:10:06 2 17:10:2006 31:12:2099 p12007:10:06 3 27:10:2006 31:12:2099 p11007:10:06 4 01:11:2006 31:12:2099 p12007:10:06 5 05:10:2006 31:12:2099 p12008:10:06 6 10:10:2006 31:12:2099 p12008:10:06 7 25:10:2006 31:12:2099 p12008:10:06 Basically there are price codes and the price , i have to find the effective price for the date today. The price must be calculated also on the latest added date. Pl. help me. I shall be very grateful. -- Regards, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with SQL Queries
Hi, Is the the right group to post for questions with SQL Queries ? thx. yashesh bhatia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with SQL Queries
hey thx for the reply.. here's my query.. http://forums.mysql.com/read.php?20,119150,119150#msg-119150 thx. yashesh bhatia. On 10/4/06, Rob Desbois [EMAIL PROTECTED] wrote: Yes, ask away :) Hi, Is the the right group to post for questions with SQL Queries ? thx. yashesh bhatia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Original Message -- FROM: Yashesh Bhatia [EMAIL PROTECTED] TO:mysql@lists.mysql.com DATE: Wed, 4 Oct 2006 17:36:38 +0530 SUBJECT: Help with SQL Queries Hi, Is the the right group to post for questions with SQL Queries ? thx. yashesh bhatia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
