DISTINCT not working inside a CASE statement.
Here is my simple table MariaDB [tutorial] select * from prices; ++--+--+ | id | name | cost | ++--+--+ | 1 | A| 1200 | | 2 | A| 2500 | | 3 | A| 3000 | | 4 | B| 5000 | | 5 | B| 7000 | | 6 | C| NULL | ++--+--+ 6 rows in set (0.00 sec) I want it to give me data as name cost A 6700 B 12000 C NULL But my query is not working - MariaDB [tutorial] select name, CASE WHEN ISNULL(DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum - from prices group by name; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum from prices group by name' at line 1 -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: DISTINCT not working inside a CASE statement.
Am 19.07.2014 13:45, schrieb Arup Rakshit: Here is my simple table MariaDB [tutorial] select * from prices; ++--+--+ | id | name | cost | ++--+--+ | 1 | A| 1200 | | 2 | A| 2500 | | 3 | A| 3000 | | 4 | B| 5000 | | 5 | B| 7000 | | 6 | C| NULL | ++--+--+ 6 rows in set (0.00 sec) I want it to give me data as name cost A 6700 B 12000 C NULL But my query is not working - MariaDB [tutorial] select name, CASE WHEN ISNULL(DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum - from prices group by name; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum from prices group by name' at line 1 how do you imagine that to work? what is the distinct in that context supposed to do? signature.asc Description: OpenPGP digital signature
Re: DISTINCT not working inside a CASE statement.
On Saturday, July 19, 2014 02:56:24 PM Reindl Harald wrote: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT sum) THEN sum(cost) ELSE NULL END AS cost_sum from prices group by name' at line 1 how do you imagine that to work? what is the distinct in that context supposed to do? Ohh. God. So simple it is - MariaDB [tutorial] select name, sum(cost) AS cost_sum from prices group by name; +--+--+ | name | cost_sum | +--+--+ | A| 6700 | | B|12000 | | C| NULL | +--+--+ 3 rows in set (0.01 sec) Thanks. -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Getting distinct values in diff columns
Something like: select aname,max(adate) group by aname; On Thu, November 22, 2012 11:06, sagar bs wrote: Hi all, I have the table with two columns(account_name and order_date). In the account_name col, some account names are only once and few account names are twice and few others are 3/4/5 times and in col2 there is order_date. For same account name there may be 2/3/4/5 different order dates. Now I need to get distinct account names, and last order date, second last order date ans so on in different columns. Please help me out to solve the issue in mysql -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Mogens Melander +66 8701 33224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
distinct count operation with the use of where count $num
Hi, I'm trying to get this work; SELECT distinct `term`,count(*) as count FROM blp_sql_distinct_temp_table where count = 5 group by `term` order by count DESC But I get this error; Unknown column 'count' in 'where clause' How do I get only those records whose group by count is above 5? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: distinct count operation with the use of where count $num
Hi, you have to use `HAVING' instead of `WHERE' like this. SELECT DISTINCT `term`, COUNT(*) AS count FROM blp_sql_distinct_temp_table GROUP BY `term` HAVING count = 5 ORDER BY count DESC; put `HAVING' next of `GROUP BY'. `WHERE' behaves at before aggregate of `GROUP BY'. your SQL means like SELECT .. FROM (SELECT * FROM .. WHERE count = 5) AS dummy GROUP BY .. because of that, mysqld says `Unknown column .. in where clause' regards, 2012/6/17 Haluk Karamete halukkaram...@gmail.com: Hi, I'm trying to get this work; SELECT distinct `term`,count(*) as count FROM blp_sql_distinct_temp_table where count = 5 group by `term` order by count DESC But I get this error; Unknown column 'count' in 'where clause' How do I get only those records whose group by count is above 5? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Howto optimize Distinct query over 1.3mil rows?
Hi, I'm running MySQL 5.0.51a-24+lenny3-log on a Debian Lenny box with 2Gb of RAM and a pair of dual core 2.6Ghz CPUs. I'm using a pair of 40Gb disks mirrored using MD (Linux software RAID) for both the OS and the storage of the mysql DBs. My problem child is doing this simple query: mysql select distinct Call_No from Newspaper_Issues mysql WHERE BIB_ID = 464; +--+ | Call_No | +--+ | News | | NewsD CT | +--+ 2 rows in set (2.98 sec) The Newspaper_Issues table has 1.3 million rows, and has a structure like this: mysql describe Newspaper_Issues; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Record_No | int(11) | NO | PRI | NULL| auto_increment | | BIB_ID | varchar(38) | NO | MUL | NULL|| | Issue_Date | date| NO | MUL | NULL|| | Type_Code | char(1) | NO | | r || | Condition_Code | char(1) | NO | | o || | Format_Code| char(1) | NO | | p || | Edition_Code | char(1) | NO | | n || | Date_Type_Code | char(1) | NO | | n || | Ed_Type| tinyint(1) | NO | | 1 || | RS_Code| char(1) | NO | | c || | Call_No| varchar(36) | YES | MUL | NULL|| | Printed_Date | varchar(10) | YES | | NULL|| | Update_Date| date| NO | | NULL|| ++-+--+-+-++ 13 rows in set (0.00 sec) I've tried adding various indexes, and reading up on howto optimize DISTINCT or GROUP BY queries, but I'm hitting a wall here. My current indexes are: mysql show index from Newspaper_Issues; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | Newspaper_Issues | 0 | PRIMARY |1 | Record_No | A | 1333298 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID |1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | Call_No |1 | Call_No | A | 927 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | Issue_Date |1 | Issue_Date | A | 49381 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_Issue_Date|1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_Issue_Date|2 | Issue_Date | A | 1333298 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | call_no_short|1 | Call_No | A | 30 |6 | NULL | YES | BTREE | | | Newspaper_Issues | 1 | BIB_ID_call_no_short |1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_call_no_short |2 | Call_No | A | 15503 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | call_no_bib_id |1 | Call_No | A | 927 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | call_no_bib_id |2 | BIB_ID | A | 15503 | NULL | NULL | | BTREE | | +--++--+--+-+---+-+--++--++-+ 11 rows in set (0.00 sec) So now when I do an explain on my query I get: mysql explain select distinct(Call_No) from Newspaper_Issues WHERE BIB_ID = 464; ++-+--+---+---++-+--+-+--+ | id | select_type | table
Re: Howto optimize Distinct query over 1.3mil rows?
BIB_ID is VARCHAR, you should probably try WHERE BIB_ID='464' so MySQL treats the value as a string JW On Tue, Sep 28, 2010 at 10:02 AM, John Stoffel j...@stoffel.org wrote: Hi, I'm running MySQL 5.0.51a-24+lenny3-log on a Debian Lenny box with 2Gb of RAM and a pair of dual core 2.6Ghz CPUs. I'm using a pair of 40Gb disks mirrored using MD (Linux software RAID) for both the OS and the storage of the mysql DBs. My problem child is doing this simple query: mysql select distinct Call_No from Newspaper_Issues mysql WHERE BIB_ID = 464; +--+ | Call_No | +--+ | News | | NewsD CT | +--+ 2 rows in set (2.98 sec) The Newspaper_Issues table has 1.3 million rows, and has a structure like this: mysql describe Newspaper_Issues; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Record_No | int(11) | NO | PRI | NULL| auto_increment | | BIB_ID | varchar(38) | NO | MUL | NULL|| | Issue_Date | date| NO | MUL | NULL|| | Type_Code | char(1) | NO | | r || | Condition_Code | char(1) | NO | | o || | Format_Code| char(1) | NO | | p || | Edition_Code | char(1) | NO | | n || | Date_Type_Code | char(1) | NO | | n || | Ed_Type| tinyint(1) | NO | | 1 || | RS_Code| char(1) | NO | | c || | Call_No| varchar(36) | YES | MUL | NULL|| | Printed_Date | varchar(10) | YES | | NULL|| | Update_Date| date| NO | | NULL|| ++-+--+-+-++ 13 rows in set (0.00 sec) I've tried adding various indexes, and reading up on howto optimize DISTINCT or GROUP BY queries, but I'm hitting a wall here. My current indexes are: mysql show index from Newspaper_Issues; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | Newspaper_Issues | 0 | PRIMARY |1 | Record_No | A | 1333298 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID |1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | Call_No |1 | Call_No | A | 927 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | Issue_Date |1 | Issue_Date | A | 49381 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_Issue_Date|1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_Issue_Date|2 | Issue_Date | A | 1333298 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | call_no_short|1 | Call_No | A | 30 |6 | NULL | YES | BTREE | | | Newspaper_Issues | 1 | BIB_ID_call_no_short |1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_call_no_short |2 | Call_No | A | 15503 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | call_no_bib_id |1 | Call_No | A | 927 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | call_no_bib_id |2 | BIB_ID | A | 15503 | NULL | NULL | | BTREE | | +--++--+--+-+---+-+--++--++-+ 11 rows in set (0.00 sec) So now when I do an explain on my query I get: mysql explain select distinct(Call_No) from Newspaper_Issues WHERE BIB_ID = 464
Re: Howto optimize Distinct query over 1.3mil rows?
If Cal_NO is a recurring value, then yes, that is the way it should be done in a relational schema. Your index cardinality of 15.000 against 1.3 million rows is reasonable, although not incredible; is your index cache large enough to acccomodate all your indices ? On Tue, Sep 28, 2010 at 5:02 PM, John Stoffel j...@stoffel.org wrote: Hi, I'm running MySQL 5.0.51a-24+lenny3-log on a Debian Lenny box with 2Gb of RAM and a pair of dual core 2.6Ghz CPUs. I'm using a pair of 40Gb disks mirrored using MD (Linux software RAID) for both the OS and the storage of the mysql DBs. My problem child is doing this simple query: mysql select distinct Call_No from Newspaper_Issues mysql WHERE BIB_ID = 464; +--+ | Call_No | +--+ | News | | NewsD CT | +--+ 2 rows in set (2.98 sec) The Newspaper_Issues table has 1.3 million rows, and has a structure like this: mysql describe Newspaper_Issues; ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | Record_No | int(11) | NO | PRI | NULL| auto_increment | | BIB_ID | varchar(38) | NO | MUL | NULL|| | Issue_Date | date| NO | MUL | NULL|| | Type_Code | char(1) | NO | | r || | Condition_Code | char(1) | NO | | o || | Format_Code| char(1) | NO | | p || | Edition_Code | char(1) | NO | | n || | Date_Type_Code | char(1) | NO | | n || | Ed_Type| tinyint(1) | NO | | 1 || | RS_Code| char(1) | NO | | c || | Call_No| varchar(36) | YES | MUL | NULL|| | Printed_Date | varchar(10) | YES | | NULL|| | Update_Date| date| NO | | NULL|| ++-+--+-+-++ 13 rows in set (0.00 sec) I've tried adding various indexes, and reading up on howto optimize DISTINCT or GROUP BY queries, but I'm hitting a wall here. My current indexes are: mysql show index from Newspaper_Issues; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | Newspaper_Issues | 0 | PRIMARY |1 | Record_No | A | 1333298 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID |1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | Call_No |1 | Call_No | A | 927 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | Issue_Date |1 | Issue_Date | A | 49381 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_Issue_Date|1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_Issue_Date|2 | Issue_Date | A | 1333298 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | call_no_short|1 | Call_No | A | 30 |6 | NULL | YES | BTREE | | | Newspaper_Issues | 1 | BIB_ID_call_no_short |1 | BIB_ID | A | 14980 | NULL | NULL | | BTREE | | | Newspaper_Issues | 1 | BIB_ID_call_no_short |2 | Call_No | A | 15503 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | call_no_bib_id |1 | Call_No | A | 927 | NULL | NULL | YES | BTREE | | | Newspaper_Issues | 1 | call_no_bib_id |2 | BIB_ID | A | 15503 | NULL | NULL | | BTREE | | +--++--+--+-+---+-+--++--++-+ 11 rows in set (0.00 sec) So now when I do an explain on my query I get
Re: Howto optimize Distinct query over 1.3mil rows?
Johnny BIB_ID is VARCHAR, you should probably try Johnny WHERE BIB_ID='464' so MySQL treats the value as a string Wow! What a difference that makes! Time to A) update my queries, or B) fix the DB schema to NOT use varchar there. mysql select SQL_NO_CACHE distinct(Call_No) from Newspaper_Issues mysql WHERE BIB_ID = 464; +--+ | Call_No | +--+ | News | | NewsD CT | +--+ 2 rows in set (3.06 sec) mysql select SQL_NO_CACHE distinct(Call_No) from Newspaper_Issues mysql WHERE BIB_ID = '464'; +--+ | Call_No | +--+ | News | | NewsD CT | +--+ 2 rows in set (0.02 sec) Thanks a ton for your help, I would have never figured this out, esp since I was looking down all the wrong rat holes. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to get distinct values in the following scenarion
MuraliKrishna wrote: Hi I have table like as following.. Cust_id Visited_date 1 2-1-2010 2 3-1-2010 3 4-1-2010 4 5-1-2010 6 6-1-2010 1 7-1-2010 2 8-1-2010 These visitor ids with visited date. but I want only all the customers with first visited date. Please help me in this.. So you want to see only the earliest date for each visitor? Try a query that looks like this: SELECT visitor_id, min(visit_date) from ... GROUP BY visitor_id; The details behind this kind of query are available from many, many sources. Here's one from our manual: http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html (in your case, you are using MIN() instead of COUNT() ) The full list of GROUP BY Functions are here: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions-and-modifiers.html -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to get distinct values in the following scenarion
Hi I have table like as following.. Cust_id Visited_date 1 2-1-2010 2 3-1-2010 3 4-1-2010 4 5-1-2010 6 6-1-2010 1 7-1-2010 2 8-1-2010 These visitor ids with visited date. but I want only all the customers with first visited date. Please help me in this.. Regards Muralikrishna
Bug? Distinct AS with Order By
I'm not sure what to search on to see if someone has reported this as a bug or if I'm doing something wrong... Generic code to draw a SELECT element on the screen sometimes it ends up like such... SELECT DISTINCT name AS myvalue,name AS mydisp FROM names WHERE name!= ORDER BY myvalue On 4.1.22 this returns A A B B C C D D On 5.0.22 this returns D D D D D D D D The odd thing is that if I remove the order by clause it works fine... It also works fine if I remove the second copy of the column BUT this is generic code so it might also be doing something like productid as myvalue,productname as mydisp where the values are different. Ultimately I can sort the array I end up with but it seems like this should work. Especially since it did in 4.x. Am I crazy? Doing something wrong? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Bug? Distinct AS with Order By
From: Matt Neimeyer m...@neimeyer.org Generic code to draw a SELECT element on the screen sometimes it ends up like such... SELECT DISTINCT name AS myvalue,name AS mydisp FROM names WHERE name!= ORDER BY myvalue On 4.1.22 this returns A A B B C C D D On 5.0.22 this returns D D D D D D D D The odd thing is that if I remove the order by clause it works fine... It also works fine if I remove the second copy of the column BUT this is generic code so it might also be doing something like productid as myvalue,productname as mydisp where the values are different. Ultimately I can sort the array I end up with but it seems like this should work. Especially since it did in 4.x. Am I crazy? Doing something wrong? Doesn't look crazy to me, and it works in 5.0.32 http://www.privatepaste.com/50RvhihKKm Perhaps time to patch that server ... Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Bug? Distinct AS with Order By
From: Glyn Astill glynast...@yahoo.co.uk Doesn't look crazy to me, and it works in 5.0.32 http://www.privatepaste.com/50RvhihKKm Perhaps time to patch that server ... I've guessed at the table def there, obviously your def may be different and that would surely affect the palanners choice. Perhaps you'd post yours? Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Distinct max() and separate unique value
I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. - Original Message - From: Eric Anderson e...@macandbumble.com To: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 3:42 PM Subject: Distinct max() and separate unique value I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
On Tue, 20 Oct 2009, DaWiz wrote: I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. You end up with the same object_id. select max(object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id order by term_taxonomy_id; ++--+ | max(object_id) | term_taxonomy_id | ++--+ | 1503 | 122 | | 1503 | 127 | ++--+ I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
- Original Message - From: Eric Anderson ke...@on-e.com To: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 4:05 PM Subject: Re: Distinct max() and separate unique value I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? I confess I did not understand what you are trying to do. If what you actually want is the highest 'term_taxonomy_id' for each distinct objhect_id then the query would be: select object_id, max(term_taxonomy_id) where term_taxonomy_id IN (122,127)group by object_id order by object_id desc; This query will not take into consideration term_taxonomy_Id values other than 122 and 127, it also will not return object_id's without a term_taxonomy_Id value of 122 or 127. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to Optimize distinct with index
Hi. I think you should create an index like this. alter table user add index idx_tmp (key1,key2,key3,user_id) 2009/6/19 Darryle Steplight dstepli...@gmail.com Select user_id from user where key1=value and key2=value2 and key3=value2 GROUP BY user_id is faster than Select distinct user_id from user where key1=value and key2=value2 and key3=value2; 2009/6/18 周彦伟 yanwei.z...@opi-corp.com: Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn
Re: How to Optimize distinct with index
Please don't change the subject on someone else's thread. Next time, post a new message instead of hitting reply on an unrelated message. In the last episode (Jun 19): Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table however. I have thousands of queries per second. How to optimize it? Because of the distinct clause, mysql has to remember all of the user_id values during the query so it can remove duplicates. You do have an index containing all of your fields, which certainly helps performance, but because the index is sorted with user_id last, mysql can't use that index to perform the distinct operation. Imagine your query returns 1000 rows with 998 unique usernames that happen to have key1 values from 2..999, and one duplicate username that happens to have rows with key1=1 and key1=1000. Because it's using the index to fetch data, rows will be sorted by key1, and the duplicate name will be in the first and last rows. Mysql needs to store the names in a temporary table to be able to remove the duplicates. Now, if user_id were first, mysql could use it directly to remove duplicates (since it would see duplicate names next to each other), but it wouldn't be able to use that index in your where clause.. :( You can't win in this case. Luckily, temporary tables aren't bad as long as they are small and mysql doesn't have to write them to disk. To be sure, run show status like 'created_%' before and after a query and see if the Created_tmp_disk_tables number increases. As long as the temp tables stay in RAM, your query will be efficient. http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Created_tmp_disk_tables Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by user_id; I add index on user_id, but after in, order use temporary table, How to optimize it? Mysql should have been able to use the index here, I think. Please post the output of create table user, a sample query, and its EXPLAIN output. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to Optimize distinct with index
Select user_id from user where key1=value and key2=value2 and key3=value2 GROUP BY user_id is faster than Select distinct user_id from user where key1=value and key2=value2 and key3=value2; 2009/6/18 周彦伟 yanwei.z...@opi-corp.com: Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to Optimize distinct with index
Hi, I have a sql : Select distinct user_id from user where key1=value and key2=value2 and key3=value2; I add index on (key1,key2,key3,user_id), this sql use temporary table howevery I have thousands of queries per second. How to optimize it? Anthoer question: Select * from user where user_id in(id1,id2,id3,id4,.) order by use_id; I add index on user_id,but after in,order use temporary table, How to optimize it? Thanks! zhouyanwei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct Query Problem
Hi Sonal, What information do you require ? Basically in the MasterTB we have productID and name, description. In the LookupTB we have the productID from the MasterTB, the productID from ProductTB and a char field for preferred supplier Yes/No. In the ProductTB we have a list of products from each supplier. This is link using the LookupTB. Let me know what other information you require. Thanks Neil On Tue, Dec 23, 2008 at 4:19 AM, Raghani, Sonal (IE10) sonal.ragh...@honeywell.com wrote: Hi, The problem statement needs to be elaborated. Please give the exact columns interms of wat is suppliers which table is it coming from etc. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Monday, December 22, 2008 5:16 PM To: Baron Schwartz; [MySQL] Subject: Re: Distinct Query Problem Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row -per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.com wrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
OK, I've made further progress by changing GROUP BY ProductTB.ProductID, MasterTB.MasterID to GROUP BY MasterTB.MasterID. However ProductTB.Supplier is showing the incorrect Supplier. Why is this ? Thanks Neil On Mon, Dec 22, 2008 at 11:45 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, If anyone could point me in the right direction, I'd be most grateful. Thanks ! Neil On Mon, Dec 22, 2008 at 9:55 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm having trouble trying to figure this out. Any help/example would be grateful. Thanks Neil On Sun, Dec 21, 2008 at 4:30 PM, Baron Schwartz ba...@xaprb.comwrote: On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. http://jan.kneschke.de/projects/mysql/groupwise-max http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: Distinct Query Problem
On Fri, Dec 19, 2008 at 1:03 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, I've the following query which I'm having problems with. Basically I have a 5 tables as follows : MasterTB - Contains list of master records LookupTB - Contains relationship between MasterTB to ProductTB ContentTB - Contains description of product, and location of data files PriceTB - Contains list of prices per day for each product ProductTB - List of products SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name, ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts FROM MasterTB INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID WHERE MasterTB.Enabled = 'Yes' AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www' AND PriceTB.Price 0 AND PriceTB.Quantity 0 GROUP BY ProductTB.ProductID, MasterTB.MasterID ORDER BY ProductTB.MarkUp DESC Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Any ideas ? Thanks, Neil You are actually going to need at least 2 queries, which will be nested. You need to first find the lowest price, then figure out which supplier has that lowest price. If more than one supplier has the same lowest price, you won't be able to do it in a single query and will likely need to do post processing. Just an example to point you in the right direction. First, get the lowest price for the product(s) you are interested in: SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice FROM PriceTB GROUP BY ProductID Then you use that as a virtual table (MinPriceList) to join on the supplier with that price for that product. SELECT ProductTB.Supplier, MinPriceList.ProductID, MinPriceList.MinPrice As PriceDiscounts FROM MasterTB INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN ( SELECT PriceTB.ProductID, MIN(PriceTB.Price) As MinPrice FROM PriceTB GROUP BY ProductID ) AS MinPriceList ON ProductTB.ProductID=MinPriceList.ProductID AND ProductTB.Price=MinPriceList.MinPrice INNER JOIN ... Basically what you are doing is creating a virtual table on the fly based on a select statement. It sort of like a temp table, but without having to go through the creation and management of it. Treat the virtual table created from the query as if it was a regular table. As I mentioned, this will break if more than one supplier has the same price. You'll get an arbitrary supplier ID out of those with the minimum price. This is because there is no unique value to join on. Hope that points you in the right direction. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Distinct Query Problem
Hi, I've the following query which I'm having problems with. Basically I have a 5 tables as follows : MasterTB - Contains list of master records LookupTB - Contains relationship between MasterTB to ProductTB ContentTB - Contains description of product, and location of data files PriceTB - Contains list of prices per day for each product ProductTB - List of products SELECT MasterTB.MasterID, ProductTB.Supplier, MasterTB.Name, ContentTB.Title, ContentTB.FolderName, MIN(PriceTB.Price) As PriceDiscounts FROM MasterTB INNER JOIN LookupTB ON LookupTB.MasterID = MasterTB.MasterID INNER JOIN ProductTB ON LookupTB.ProductID = ProductTB.ProductID INNER JOIN PriceTB ON ProductTB.ProductID = PriceTB.ProductID INNER JOIN ContentTB ON ProductTB.ProductID = ContentTB.ProductID WHERE MasterTB.Enabled = 'Yes' AND ContentTB.Language = 'ENG' AND ContentTB.Site = 'www' AND PriceTB.Price 0 AND PriceTB.Quantity 0 GROUP BY ProductTB.ProductID, MasterTB.MasterID ORDER BY ProductTB.MarkUp DESC Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Any ideas ? Thanks, Neil
Re: Distinct Query Problem
On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote: Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Search the manual for group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
trying to figure out unique/distinct situation...
hey... got a quick situation where i'm trying to figure out how to do a unique/distinct so i get two (2) rows for the 'faculty_id'... so.. how can i get just two unique/distinct rows based on the faculty_id thanks mysql select distinct f1_status.faculty_id, c1.cname, course1.cname, f1_status.userID, c1.id, f1_status.id - from c1 - join d1 - on c1.id=d1.cID - join course1 - on d1.id=course1.dID - join f1_status - on course1.fID=f1_status.faculty_id - where c1.id=1 and d1.id=1; ++---+-++++ | faculty_id | cname | cname | userID | id | id | ++---+-++++ | 1 | usc | math-101| 1 | 1 | 1 | | 2 | usc | math-202| 2 | 1 | 2 | | 1 | usc | physics-101 | 1 | 1 | 1 | ++---+-++++ 3 rows in set (0.00 sec) == mysql select c1.cname, course1.cname, f1_status.faculty_id, f1_status.userID, c1.id, f1_status.id - from c1 - join d1 - on c1.id=d1.cID - join course1 - on d1.id=course1.dID - join f1_status - on course1.fID=f1_status.faculty_id - where c1.id=1 and d1.id=1; +---+-+++++ | cname | cname | faculty_id | userID | id | id | +---+-+++++ | usc | math-101| 1 | 1 | 1 | 1 | | usc | math-202| 2 | 2 | 1 | 2 | | usc | physics-101 | 1 | 1 | 1 | 1 | +---+-+++++ 3 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re[4]: SELECT DISTINCT with ORDER BY implementation
Hi! Andrew == Andrew Aksyonoff [EMAIL PROTECTED] writes: Andrew Hello Sergey, Andrew Monday, September 15, 2008, 10:41:31 PM, you wrote: in MySQL but in general case it can't assume any order and will have to re-sort the sub-select result by outer GROUP BY instead of inner ORDER BY. If that sorting is stable, this should work, but can we rely SP Yes. This is documented behavior: SP http://dev.mysql.com/doc/refman/5.0/en/select.html : SP If you use GROUP BY, output rows are sorted according to the GROUP BY SP columns as if you had an ORDER BY for the same columns. To avoid the SP overhead of sorting that GROUP BY produces, add ORDER BY NULL: Andrew Well, this snippet documents how the *grouped* rows will be ordered, Andrew but the question is about the properties of specific sorting algorithm Andrew that is internally used to implement GROUP BY. Andrew I'm not sure if I'm clear enough so let me provide an example. Assume Andrew that the inner SELECT produces the following: Andrew id=1, sortkey=123, groupkey=33 Andrew id=2, sortkey=124, groupkey=33 Andrew id=3, sortkey=125, groupkey=11 Andrew id=4, sortkey=126, groupkey=11 Andrew id=5, sortkey=127, groupkey=22 Andrew id=6, sortkey=128, groupkey=22 Andrew I suppose that 'GROUP BY groupkey' will have to sort the incoming Andrew rows by groupkey, then go over it sequentially, keeping only the Andrew first encountered row for every given groupkey. That is one algoritm, but MySQL has others. Andrew However if the specific sorting algorithm is not stable it *might* Andrew change the order and produce something like that for temporary Andrew sorted set: It's not stable; MySQL is using several different technics to calculate GROUP BY and may thus return the rows in any order within the group by. Andrew id=4, sortkey=126, groupkey=11 Andrew id=3, sortkey=125, groupkey=11 Andrew id=6, sortkey=128, groupkey=22 Andrew id=5, sortkey=127, groupkey=22 Andrew id=1, sortkey=123, groupkey=33 Andrew id=2, sortkey=124, groupkey=33 Andrew And put id=4 instead of id=3 into the result set. Andrew So the question is a bit more subtle :) It's whether the algorithm Andrew that GROUP BY (and possibly everything else) uses stable or not. Andrew I'd bet a quarter that it is but just want to make sure :) In general with SQL: Don't assume any order of rows if you don't explicitely specify a sort order. You can send the quarter to 'the well being of dolphin fund'. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[5]: SELECT DISTINCT with ORDER BY implementation
Hello Michael, Tuesday, September 30, 2008, 8:00:36 PM, you wrote: Andrew However if the specific sorting algorithm is not stable it *might* MW It's not stable; MySQL is using several different technics to MW calculate GROUP BY and may thus return the rows in any order within MW the group by. Thanks for clarification. MW In general with SQL: Don't assume any order of rows if you don't MW explicitely specify a sort order. Sure. But sometimes, knowing how the system works internally, we can still make a kludge. -- Best regards, Andrewmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling a second mysql binary with a distinct configuration.
I'm running mysql-5.0.46 on a linux system on a VPS. As such, the yum package
manager is being used to install a standardized mysql installation, and I due to
other system dependencies, I can't change this.
I need a second mysql installation on that machine that runs under a completely
different directory tree and is totally separate in all ways from the standard
installation. Therefore, I downloaded the source and compiled it to install
itself under the /usr/private directory tree. I did this as follows:
dir=/usr/private
./configure \
--prefix=${dir}\
--exec-prefix=${dir} \
--bindir=${dir}/bin\
--sbindir=${dir}/sbin \
--libexecdir=${dir}/libexec\
--datadir=${dir}/share \
--sysconfdir=${dir}/etc\
--sharedstatedir=${dir}/var\
--localstatedir=${dir}/var \
--libdir=${dir}/lib\
--includedir=${dir}/include\
--oldincludedir=${dir}/include \
--infodir=${dir}/info \
--mandir=${dir}/man
I did a make followed by a make install, and everything indeed got
installed under the /usr/private tree. However, the various programs
still seem to be looking by default in /etc for the my.cnf file. Since
/etc/my.cnf is the file that the standard version of mysql uses, this new,
private version tries to point to the directories that are specified in the
old version's configuration, which is clearly not what I want it to do.
I was hoping to get it to look for a second my.cnf file inside of
/usr/private/etc, but that isn't happening.
It seems like the build is ignoring the --sysconfdir parameter, at least
with regard to the location of the my.cnf file.
What am I doing wrong? Or is this some sort of deliberate feature of mysql?
Thanks in advance for any light you can shed on this.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alternative to subquery to perform distinct aggregation in query
Hi, I have a query at the moment like this: SELECT SQL_NO_CACHE STRAIGHT_JOIN t1.col1, t1.col2, t2.col1, ... MAX(t1.col6)... ( SELECT Count(DISTINCT col1) FROM table3 t3 WHERE t3.col1 = t1.col1 AND t3.col2 = t1.col2 AND t3.col1 IN (139903,140244,140583,140924,141265,141601,141942) ) AS uniquecount FROM table1 t1 INNER JOIN table2 t2 ON t1.col6 = t2.id WHERE t2.id IN (139903,140244,140583,140924,141265,141601,141942) GROUP BY t1.col1, t1.col2, t1.col3 ORDER BY NULL Basically, you can tell that theres a main table with information that's aggregated, and then another table with matching rows to aggregate too - per row for the first table. This appears very slow. I've tried running the queries separately and they appear to be performing better on their own (as somewhat expected). Does anyone have any ideas on how to optimize the above query? I think I will just go with the latter dual query approach as it is more gentle on the database server too. Cheers, Andrew
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: Finding a Solution To A Distinct Problem of Mine
Ahh... sweet... makes sense... thanks! :-) On 3/12/07, Brent Baisley [EMAIL PROTECTED] wrote: If you are looking for the latest created_at date, then you want to be grabbing the max value of that field. SELECT DISTINCT from_user_id, max(created_at) FROM messages WHERE to_user_id = 1 GROUP BY from_user_id; In your original query I think you meant to select from, not to, since to will be 1; - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, March 11, 2007 12:59 PM Subject: Re: Finding a Solution To A Distinct Problem of Mine I think I got it: SELECT * FROM (SELECT * FROM messages ORDER BY created_at DESC) as messages WHERE to_user_id = 1 GROUP BY from_user_id; Is this the best way about it... or are their better ways you suggest? On 3/11/07, John Kopanas [EMAIL PROTECTED] wrote: I have the following table: messages: if: from_user_id: to_user_id: body: created_at: updated_at: I have to return all the rows that have a distinct from_user_id based on a to_user_id. Not only that but I want to return the rows with the newest created_at. I thought this would work: SELECT DISTINCT to_user_id, created_at FROM messages WHERE to_user_id = 1 ORDER BY created_at; But the problem is that I only get distincts when I only have to_user_id in the SELECT clause. Any suggestions. I need to return everything on the latest row that has a distinct from_user_id :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- 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]
Re: Finding a Solution To A Distinct Problem of Mine
If you are looking for the latest created_at date, then you want to be grabbing the max value of that field. SELECT DISTINCT from_user_id, max(created_at) FROM messages WHERE to_user_id = 1 GROUP BY from_user_id; In your original query I think you meant to select from, not to, since to will be 1; - Original Message - From: John Kopanas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, March 11, 2007 12:59 PM Subject: Re: Finding a Solution To A Distinct Problem of Mine I think I got it: SELECT * FROM (SELECT * FROM messages ORDER BY created_at DESC) as messages WHERE to_user_id = 1 GROUP BY from_user_id; Is this the best way about it... or are their better ways you suggest? On 3/11/07, John Kopanas [EMAIL PROTECTED] wrote: I have the following table: messages: if: from_user_id: to_user_id: body: created_at: updated_at: I have to return all the rows that have a distinct from_user_id based on a to_user_id. Not only that but I want to return the rows with the newest created_at. I thought this would work: SELECT DISTINCT to_user_id, created_at FROM messages WHERE to_user_id = 1 ORDER BY created_at; But the problem is that I only get distincts when I only have to_user_id in the SELECT clause. Any suggestions. I need to return everything on the latest row that has a distinct from_user_id :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding a Solution To A Distinct Problem of Mine
I have the following table: messages: if: from_user_id: to_user_id: body: created_at: updated_at: I have to return all the rows that have a distinct from_user_id based on a to_user_id. Not only that but I want to return the rows with the newest created_at. I thought this would work: SELECT DISTINCT to_user_id, created_at FROM messages WHERE to_user_id = 1 ORDER BY created_at; But the problem is that I only get distincts when I only have to_user_id in the SELECT clause. Any suggestions. I need to return everything on the latest row that has a distinct from_user_id :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a Solution To A Distinct Problem of Mine
I think I got it: SELECT * FROM (SELECT * FROM messages ORDER BY created_at DESC) as messages WHERE to_user_id = 1 GROUP BY from_user_id; Is this the best way about it... or are their better ways you suggest? On 3/11/07, John Kopanas [EMAIL PROTECTED] wrote: I have the following table: messages: if: from_user_id: to_user_id: body: created_at: updated_at: I have to return all the rows that have a distinct from_user_id based on a to_user_id. Not only that but I want to return the rows with the newest created_at. I thought this would work: SELECT DISTINCT to_user_id, created_at FROM messages WHERE to_user_id = 1 ORDER BY created_at; But the problem is that I only get distincts when I only have to_user_id in the SELECT clause. Any suggestions. I need to return everything on the latest row that has a distinct from_user_id :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a Solution To A Distinct Problem of Mine
Ok, did not work... hmmm... any other suggestions? On 3/11/07, John Kopanas [EMAIL PROTECTED] wrote: I think I got it: SELECT * FROM (SELECT * FROM messages ORDER BY created_at DESC) as messages WHERE to_user_id = 1 GROUP BY from_user_id; Is this the best way about it... or are their better ways you suggest? On 3/11/07, John Kopanas [EMAIL PROTECTED] wrote: I have the following table: messages: if: from_user_id: to_user_id: body: created_at: updated_at: I have to return all the rows that have a distinct from_user_id based on a to_user_id. Not only that but I want to return the rows with the newest created_at. I thought this would work: SELECT DISTINCT to_user_id, created_at FROM messages WHERE to_user_id = 1 ORDER BY created_at; But the problem is that I only get distincts when I only have to_user_id in the SELECT clause. Any suggestions. I need to return everything on the latest row that has a distinct from_user_id :-). -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql question regarding distinct/group by...
Hi, dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 Try, select * from dog where fooID=1 group by fooID; - which retrieves the first instance; resulting in, 1,2,1 Thanks ViSolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 6:07 AM Subject: mysql question regarding distinct/group by... hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql question regarding distinct/group by...
SELECT * FROM Dog GROUP BY FooId HAVING FooId = 1; This should also work. On 1/5/07, ViSolve DB Team [EMAIL PROTECTED] wrote: Hi, dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 Try, select * from dog where fooID=1 group by fooID; - which retrieves the first instance; resulting in, 1,2,1 Thanks ViSolve DB Team - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, January 04, 2007 6:07 AM Subject: mysql question regarding distinct/group by... hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
mysql question regarding distinct/group by...
hi... i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql question regarding distinct/group by...
In the last episode (Jan 03), bruce said: i've asked something similar before.. but it appears something is going wrong... so, back to basics... i have the following test tbl. dog fooID int size int id int dog fooIDsizeid 1 2 1 2 5 2 1 5 3 if i do a query select * from dog where fooID='1'; i get 1,2,1 1,5,3 how can i do a distinct/group by select such that if i do a select on fooID=1, i'll only get '1,2,1', or '1,5,3', meaning that i don't get both of the items where fooId=1. How about just asking for the first matching record with LIMIT 1? -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
The table structure is in my original post. On Dec 14, 2006, at 11:49 AM, Rolando Edwards wrote: Just change your table name and you are all set. If you have the table structure, send it and I'll make the query if you want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Workaround for distinct?
+++ | id | color | +++ | 1 | red| | 2 | blue | | 3 | red| | 4 | yellow | | 5 | yellow | | 6 | blue | | .. | ...| I'm trying to select 5 random records, but no more than 1 of any given color. According to the notes in the documentation and to my own testing (I'm on v4.x), this doesn't work: select id,distinct(color) from tablename order by rand(); I found that using 'group by color' works to limit it to one of each color, but the problem is I always get the same record of each color. I need to mix it up and give me different random records every time: select id,color from tablename group by color order by rand(); This should be so easy!! What's the obvious solution that I'm missing? - Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
I tried this out this morning on MySQL 5.
It works. Please try this in MySQL 4 and see.
drop table if exists color_table;
create table color_table (id int not null auto_increment,color
varchar(10),primary key (id));
--
-- Loading Color Data
--
insert into color_table (color) values
('red'),('blue'),('red'),('yellow'),('yellow'),('blue'),
('green'),('brown'),('green'),('yellow'),('orange'),('blue'),
('pink'),('blue'),('red'),('brown'),('pink'),('blue');
--
-- Full Listing of Table
--
select * from color_table;
--
-- Listing of Colors with the Mininum ID for the Color
--
select min(id),color from color_table group by color;
--
-- Randomizing the Color Listing 5 Times
--
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
select A.id,A.color from (select min(id) id,color from color_table group by
color) A order by rand();
Give It A Try !!!
- Original Message -
From: Brian Dunning [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, December 14, 2006 11:15:39 AM GMT-0500 US/Eastern
Subject: Workaround for distinct?
+++
| id | color |
+++
| 1 | red|
| 2 | blue |
| 3 | red|
| 4 | yellow |
| 5 | yellow |
| 6 | blue |
| .. | ...|
I'm trying to select 5 random records, but no more than 1 of any
given color. According to the notes in the documentation and to my
own testing (I'm on v4.x), this doesn't work:
select id,distinct(color) from tablename order by rand();
I found that using 'group by color' works to limit it to one of each
color, but the problem is I always get the same record of each color.
I need to mix it up and give me different random records every time:
select id,color from tablename group by color order by rand();
This should be so easy!! What's the obvious solution that I'm missing?
- Brian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
I tried this out this morning on MySQL 5. It works. Please try this in MySQL 4 and see. Unless I'm way off, I do not believe your solution will work in 4.x because it doesn't support sub-queries... thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
On Thu, 14 Dec 2006, Chris Boget wrote: =I tried this out this morning on MySQL 5. = It works. Please try this in MySQL 4 and see. = =Unless I'm way off, I do not believe your solution will work in 4.x because =it doesn't support sub-queries... = 4.1 does. See http://dev.mysql.com/doc/refman/4.1/en/subqueries.html -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
Here onoe that should work. It only uses a LEFT JOIN It does not use Subqueries It does not use DISTINCT Here it is : select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color and A.idB.id group by A.color order by rand(); Give it a Try !!! - Original Message - From: Dwalu Z. Khasu [EMAIL PROTECTED] To: Chris Boget [EMAIL PROTECTED] Cc: Rolando Edwards [EMAIL PROTECTED], Brian Dunning [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:17:30 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? On Thu, 14 Dec 2006, Chris Boget wrote: =I tried this out this morning on MySQL 5. = It works. Please try this in MySQL 4 and see. = =Unless I'm way off, I do not believe your solution will work in 4.x because =it doesn't support sub-queries... = 4.1 does. See http://dev.mysql.com/doc/refman/4.1/en/subqueries.html -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
Thanks, but the table structure that I have to work with cannot be changed. Any solution with the simple table I listed? On Dec 14, 2006, at 9:30 AM, Rolando Edwards wrote: Here onoe that should work. It only uses a LEFT JOIN It does not use Subqueries It does not use DISTINCT Here it is : select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color and A.idB.id group by A.color order by rand(); Give it a Try !!! - Original Message - From: Dwalu Z. Khasu [EMAIL PROTECTED] To: Chris Boget [EMAIL PROTECTED] Cc: Rolando Edwards [EMAIL PROTECTED], Brian Dunning [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:17:30 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? On Thu, 14 Dec 2006, Chris Boget wrote: =I tried this out this morning on MySQL 5. = It works. Please try this in MySQL 4 and see. = =Unless I'm way off, I do not believe your solution will work in 4.x because =it doesn't support sub-queries... = 4.1 does. See http://dev.mysql.com/doc/refman/4.1/en/subqueries.html -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
This works also select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color group by A.color order by rand(); - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Dwalu Z. Khasu [EMAIL PROTECTED] Cc: Brian Dunning [EMAIL PROTECTED], mysql@lists.mysql.com, Chris Boget [EMAIL PROTECTED] Sent: Thursday, December 14, 2006 12:30:02 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? Here onoe that should work. It only uses a LEFT JOIN It does not use Subqueries It does not use DISTINCT Here it is : select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color and A.idB.id group by A.color order by rand(); Give it a Try !!! - Original Message - From: Dwalu Z. Khasu [EMAIL PROTECTED] To: Chris Boget [EMAIL PROTECTED] Cc: Rolando Edwards [EMAIL PROTECTED], Brian Dunning [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:17:30 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? On Thu, 14 Dec 2006, Chris Boget wrote: =I tried this out this morning on MySQL 5. = It works. Please try this in MySQL 4 and see. = =Unless I'm way off, I do not believe your solution will work in 4.x because =it doesn't support sub-queries... = 4.1 does. See http://dev.mysql.com/doc/refman/4.1/en/subqueries.html -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Workaround for distinct?
I think got it now !!! If you run this query multiple times, you will get a list of colors all the colors and corresponding IDs ordered by Color, and randomly ordered within color. Here is the query: select AAA.color,AAA.bid id from (select AA.color,AA.id,AA.bid from (select min(A.id) id,B.id bid,A.color from color_table A,color_table B where A.color=B.color and A.id=B.id group by A.color,B.id) AA order by 1,2,rand()) AAA ; If you add a WHERE clause to the AAA alias and specify a color, the query will indeed give you a random ID for the given color each time you run it. Here is that query: select AAA.color,AAA.bid id from (select AA.color,AA.id,AA.bid from (select min(A.id) id,B.id bid,A.color from color_table A,color_table B where A.color=B.color and A.id=B.id group by A.color,B.id) AA order by 1,2,rand()) AAA where AAA.color='red' LIMIT 1 ; Just change your table name and you are all set. If you have the table structure, send it and I'll make the query if you want. - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Brian Dunning [EMAIL PROTECTED], Chris Boget [EMAIL PROTECTED], Dwalu Z. Khasu [EMAIL PROTECTED] Sent: Thursday, December 14, 2006 12:34:05 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? This works also select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color group by A.color order by rand(); - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: Dwalu Z. Khasu [EMAIL PROTECTED] Cc: Brian Dunning [EMAIL PROTECTED], mysql@lists.mysql.com, Chris Boget [EMAIL PROTECTED] Sent: Thursday, December 14, 2006 12:30:02 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? Here onoe that should work. It only uses a LEFT JOIN It does not use Subqueries It does not use DISTINCT Here it is : select min(A.id) id,A.color from color_table A left join color_table B on A.color=B.color and A.idB.id group by A.color order by rand(); Give it a Try !!! - Original Message - From: Dwalu Z. Khasu [EMAIL PROTECTED] To: Chris Boget [EMAIL PROTECTED] Cc: Rolando Edwards [EMAIL PROTECTED], Brian Dunning [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:17:30 PM GMT-0500 US/Eastern Subject: Re: Workaround for distinct? On Thu, 14 Dec 2006, Chris Boget wrote: =I tried this out this morning on MySQL 5. = It works. Please try this in MySQL 4 and see. = =Unless I'm way off, I do not believe your solution will work in 4.x because =it doesn't support sub-queries... = 4.1 does. See http://dev.mysql.com/doc/refman/4.1/en/subqueries.html -- - Dwalu .peace -- I am an important person in this world - Now is the most important time in my life - My mistakes are my best teachers - So I will be fearless. - Student Creed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating new table from distinct entries
Hi, You will get a MySQL query syntax error number 1064 when you incorrectly use a reserved words in your query . * and DISTINCT cannot appear together. Check with the SELECT clause column list. Thanks ViSolve DB Team. - Original Message - From: Alan Milnes [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 24, 2006 12:47 AM Subject: Creating new table from distinct entries MySQL 4.1.21-community-nt I have a table in my database that has a Primary key on 2 fields (MyID and MyChange) and a field that indicates if there is a problem with the record (MyError)- I want to create a new table that only has unique MyIDs and where there is more than 1 I only want the record with the highest MyChange number. The table has about 50 fields so I have the following code:- CREATE TABLE mystats SELECT *, DISTINCT MyID FROM oldstats WHERE MyError IS NULL ORDER BY MyChange DESC ; but I am getting an MySQL error #1064. Any ideas or suggestions as to where I am going wrong? Alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating new table from distinct entries
MySQL 4.1.21-community-nt I have a table in my database that has a Primary key on 2 fields (MyID and MyChange) and a field that indicates if there is a problem with the record (MyError)- I want to create a new table that only has unique MyIDs and where there is more than 1 I only want the record with the highest MyChange number. The table has about 50 fields so I have the following code:- CREATE TABLE mystats SELECT *, DISTINCT MyID FROM oldstats WHERE MyError IS NULL ORDER BY MyChange DESC ; but I am getting an MySQL error #1064. Any ideas or suggestions as to where I am going wrong? Alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum DISTINCT
Dear friends: I have two tables. In one of those I store information about loans and the year it was given. The other one stores the families which have received this loans, with infromation abot the number of members in each family (male and women). Each family can receive more than one loan in a year. The query I need to get is the total of male and women which have got a loan during an especific year, making a join betwen this two tables. La consulta que necesito obtener es: Suma de hombres y mujeres de las familias que han participado de algún crédito durante cada año. SELECT credito.anio, Sum(familia.hombres) AS Total_Hombres, Sum(familia.mujeres) AS Total_Mujeres FROM credito LEFT JOIN familia ON credito.id_familia = familia.id_familia GROUP BY credito.anio; +--+---+---+ | anio | Total_Hombres | Total_Mujeres | +--+---+---+ | 2005 |21 |23 | | 2006 |11 | 9 | +--+---+---+ (Translation: Hombre=Male; Mujeres=Women) The problem is that as long as the families can receive more than one loan in one year (so there are more than one row in the families/loan row), the rows of the male and women get duplicated so the SUM of those results it too big (for example, in the upper result table Male might be 12 instead of 21). So is there a function like a DISTINCT to sum each different family in this joined tables?. Thanks a lot and best regards, Alvaro Cobo MySQL version 5.0.17 SO: Debian Sarge. Tabla de ejemplo. Base de datos test. CREATE TABLE `familia` ( `id_familia` int(11) NOT NULL auto_increment, `nombre_familia` varchar(60) NOT NULL, `hombres` int(11) NOT NULL, `mujeres` int(11) NOT NULL, PRIMARY KEY (`id_familia`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; INSERT INTO `familia` VALUES (1, 'Perez', 2, 3); INSERT INTO `familia` VALUES (2, 'Suarez', 5, 3); INSERT INTO `familia` VALUES (3, 'Sanchez', 6, 6); INSERT INTO `familia` VALUES (4, 'Montalvo', 4, 5); INSERT INTO `familia` VALUES (5, 'Cobo', 4, 3); INSERT INTO `familia` VALUES (6, 'Larrea', 1, 3); CREATE TABLE `credito` ( `anio` year(4) NOT NULL, `id_credito` int(11) NOT NULL auto_increment, `id_familia` int(11) NOT NULL, PRIMARY KEY (`id_credito`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ; INSERT INTO `credito` VALUES (2005, 1, 1); INSERT INTO `credito` VALUES (2005, 2, 1); INSERT INTO `credito` VALUES (2005, 3, 1); INSERT INTO `credito` VALUES (2005, 4, 3); INSERT INTO `credito` VALUES (2005, 5, 4); INSERT INTO `credito` VALUES (2005, 6, 2); INSERT INTO `credito` VALUES (2006, 7, 2); INSERT INTO `credito` VALUES (2006, 8, 3); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
Why don't you just use a GROUP BY on lat,long? You could try using CONCAT: select distinct(CONCAT(lat, long)) from table where ... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Lat lon are two different fields. Either can be duplicated, but not both. On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
But if I do this, how do I still get lat and lon as two different fields? This finds the right record set, but it returns both fields concatenated into a single field. On Sep 12, 2006, at 12:46 PM, Steve Musumeche wrote: You could try using CONCAT: select distinct(CONCAT(lat, long)) from table where ... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Lat lon are two different fields. Either can be duplicated, but not both. On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
Never mind, I figured it out: select distinct(concat(lat,lon)), lat, lon where On Sep 13, 2006, at 6:57 AM, Brian Dunning wrote: But if I do this, how do I still get lat and lon as two different fields? This finds the right record set, but it returns both fields concatenated into a single field. On Sep 12, 2006, at 12:46 PM, Steve Musumeche wrote: You could try using CONCAT: select distinct(CONCAT(lat, long)) from table where ... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Lat lon are two different fields. Either can be duplicated, but not both. On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Distinct select over 2 fields?
I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
Lat lon are two different fields. Either can be duplicated, but not both. On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct select over 2 fields?
You could try using CONCAT: select distinct(CONCAT(lat, long)) from table where ... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Lat lon are two different fields. Either can be duplicated, but not both. On Sep 12, 2006, at 12:33 PM, Steve Musumeche wrote: Select DISTINCT(lat_long_field) from table where... Steve Musumeche CIO, Internet Retail Connection [EMAIL PROTECTED] Brian Dunning wrote: Many different records will be returned though, I just don't want any dupes where both lat/lon is the same. :) On Sep 12, 2006, at 12:20 PM, Hiep Nguyen wrote: select * from table where . limit 1 that would do it if you don't care which one it returns JC On Tue, 12 Sep 2006, Brian Dunning wrote: I'm searching a database of geopoints, and when two records have the same latitude and longitude, I only want to return one of them - basically just find all the unique locations. How do you set up a select like this? Thanks... -- --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does DISTINCT kill ORDER BY?
Markus Hoenicka wrote: Hi, is the following behaviour intended? Are my queries wrong? The output shows only the Extra field as the other fields are identical in all cases. EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using filesort (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where (results are not sorted) That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? ie: select count(distinct t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does DISTINCT kill ORDER BY?
Chris [EMAIL PROTECTED] was heard to say: That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; = 784 SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE t_refdb.refdb_id0; = 784 In this case refdb_type does not further restrict the result set. However, I've tried queries where a modified clause further restricts the results selected by refdb_id with the same effect regarding the sorting. BTW the above results were obtained with mysql.exe Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32) so the problem is neither specific to the FreeBSD port nor to a particular 4.1.x version. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does DISTINCT kill ORDER BY?
Just for the archives: looks like I bumped into bug #21456: http://bugs.mysql.com/bug.php?id=21456 This bug has been fixed in 4.1.22 and 5.0.25, so I'll just have to upgrade. Thanks anyway Markus Markus Hoenicka [EMAIL PROTECTED] was heard to say: Chris [EMAIL PROTECTED] was heard to say: That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; = 784 SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE t_refdb.refdb_id0; = 784 In this case refdb_type does not further restrict the result set. However, I've tried queries where a modified clause further restricts the results selected by refdb_id with the same effect regarding the sorting. BTW the above results were obtained with mysql.exe Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32) so the problem is neither specific to the FreeBSD port nor to a particular 4.1.x version. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does DISTINCT kill ORDER BY?
Hi, is the following behaviour intended? Are my queries wrong? The output shows only the Extra field as the other fields are identical in all cases. EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using filesort (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where (results are not sorted) That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. These results were obtained with: mysql Ver 14.7 Distrib 4.1.21, for portbld-freebsd6.1 (i386) using 5.0 FreeBSD yeti.mininet 6.1-RELEASE FreeBSD 6.1-RELEASE #1: Mon Aug 28 22:24:48 CEST 2006 [EMAIL PROTECTED]:/usr/src/sys/i386/compile/YETI i386 regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct problem
the below query worked great in mysql 3.23, but we just moved to 5.0 and it broke, i can see that the join rules changed in 5.0, but i can't get the right syntax to make this query work. any help would be appreciated. On 6/28/06, Peter Brawley [EMAIL PROTECTED] wrote: Tanner I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC SELECT t1.item_id, t1.dt, t1.text FROM table AS t1 LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; PB - Tanner Postert wrote: The situation is somewhat hard to describe, so please bare with me: I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC here is an example record set. text1,2006-06-28 10:00:00,4 text2,2006-06-28 10:15:00,4 text3,2006-06-28 10:30:00,8 text4,2006-06-28 11:00:00,8 the results from the above query would be: text1,2006-06-28 10:00:00,4 text3,2006-06-28 10:30:00,8 my problem is that i want the other item to show up. the item with the most recent DT. it is doing the grouping before it does the ordering. how do i specify that I want to see the most recent info when it does the group? thanks in advance. Tanner -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
Re: Distinct problem
I actually solved my own problem... SELECT t1.item_id, t1.dt, t1.text ,t3.* FROM table AS t1, table3 as t3 LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; becomes SELECT t1.item_id, t1.dt, t1.text FROM (table AS t1, table3 as t3) LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; amazing what a little set of parenthesis will do. thanks anyways. On 7/18/06, Tanner Postert [EMAIL PROTECTED] wrote: the below query worked great in mysql 3.23, but we just moved to 5.0 and it broke, i can see that the join rules changed in 5.0, but i can't get the right syntax to make this query work. any help would be appreciated. On 6/28/06, Peter Brawley [EMAIL PROTECTED] wrote: Tanner I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC SELECT t1.item_id, t1.dt, t1.text FROM table AS t1 LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; PB - Tanner Postert wrote: The situation is somewhat hard to describe, so please bare with me: I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC here is an example record set. text1,2006-06-28 10:00:00,4 text2,2006-06-28 10:15:00,4 text3,2006-06-28 10:30:00,8 text4,2006-06-28 11:00:00,8 the results from the above query would be: text1,2006-06-28 10:00:00,4 text3,2006-06-28 10:30:00,8 my problem is that i want the other item to show up. the item with the most recent DT. it is doing the grouping before it does the ordering. how do i specify that I want to see the most recent info when it does the group? thanks in advance. Tanner -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006
Re: Distinct problem
Tanner Postert wrote: I actually solved my own problem... SELECT t1.item_id, t1.dt, t1.text ,t3.* FROM table AS t1, table3 as t3 LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; becomes SELECT t1.item_id, t1.dt, t1.text FROM (table AS t1, table3 as t3) LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; amazing what a little set of parenthesis will do. thanks anyways. t3 is not joined at all. Re-write this using inner joins to see your problem. SELECT t1.item_id, t1.dt, t1.text FROM table AS t1 INNER JOIN table3 AS t3 ON ??? LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Distinct from two tables
Is there a way get distinct results between pf and sf? Select pf.name, sf.name From tblpropertyfeatures, tblsuitefeatures Inner Join tblfeatures AS pf ON tblpropertyfeatures.featureid = pf.id Inner Join tblfeatures AS sf ON tblsuitefeatures.featureid = sf.id - Mark Steudel NetRiver Web and Application Developer 555 Dayton St. Suite A Edmonds, WA 98020 w: http://www.netriver.net p: 425.741.7014 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct from two tables
Mark Steudel wrote: Is there a way get distinct results between pf and sf? Select pf.name, sf.name From tblpropertyfeatures, tblsuitefeatures Inner Join tblfeatures AS pf ON tblpropertyfeatures.featureid = pf.id Inner Join tblfeatures AS sf ON tblsuitefeatures.featureid = sf.id Do you mean ... SELECT MIN(TableName) as TableName, id, name FROM ( SELECT 'Table a' as TableName, a.id, a.name FROM tblpropertyfeatures UNION ALL SELECT 'Table b' as TableName, b.id, b.name FROM tblsuitefeatures ) AS tmp GROUP BY id, name HAVING COUNT(*) = 1 ORDER BY ID; PB - Mark Steudel NetRiver Web and Application Developer 555 Dayton St. Suite A Edmonds, WA 98020 w: http://www.netriver.net p: 425.741.7014 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.7/379 - Release Date: 6/29/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Distinct problem
The situation is somewhat hard to describe, so please bare with me: I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC here is an example record set. text1,2006-06-28 10:00:00,4 text2,2006-06-28 10:15:00,4 text3,2006-06-28 10:30:00,8 text4,2006-06-28 11:00:00,8 the results from the above query would be: text1,2006-06-28 10:00:00,4 text3,2006-06-28 10:30:00,8 my problem is that i want the other item to show up. the item with the most recent DT. it is doing the grouping before it does the ordering. how do i specify that I want to see the most recent info when it does the group? thanks in advance. Tanner
Re: Distinct problem
Use the MAX() function, like so: select text, MAX(dt) as dt, item_id from table where group by item_id order by dt DESC Dan On 6/28/06, Tanner Postert [EMAIL PROTECTED] wrote: The situation is somewhat hard to describe, so please bare with me: I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC here is an example record set. text1,2006-06-28 10:00:00,4 text2,2006-06-28 10:15:00,4 text3,2006-06-28 10:30:00,8 text4,2006-06-28 11:00:00,8 the results from the above query would be: text1,2006-06-28 10:00:00,4 text3,2006-06-28 10:30:00,8 my problem is that i want the other item to show up. the item with the most recent DT. it is doing the grouping before it does the ordering. how do i specify that I want to see the most recent info when it does the group? thanks in advance. Tanner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Distinct problem
Tanner I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC SELECT t1.item_id, t1.dt, t1.text FROM table AS t1 LEFT JOIN table AS t2 ON t1.item_id = t2.item_id AND t1.dt t2.dt WHERE t2.item_id IS NULL; PB - Tanner Postert wrote: The situation is somewhat hard to describe, so please bare with me: I am trying to group my results by the last activity on each row, my query looks like this select text, dt, item_id from table where group by item_id order by dt DESC here is an example record set. text1,2006-06-28 10:00:00,4 text2,2006-06-28 10:15:00,4 text3,2006-06-28 10:30:00,8 text4,2006-06-28 11:00:00,8 the results from the above query would be: text1,2006-06-28 10:00:00,4 text3,2006-06-28 10:30:00,8 my problem is that i want the other item to show up. the item with the most recent DT. it is doing the grouping before it does the ordering. how do i specify that I want to see the most recent info when it does the group? thanks in advance. Tanner No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.5/377 - Release Date: 6/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need a query to show distinct IP dotted quad components
I have a table of many IP addresses. I'm doing some PHP/JS/AJAX to populate a select box based upon what someone types in a search field. That works great, except that a user can spend a lot of time guessing as to what possible IPs exist. What I'd like to do now is one of those google suggestions thingys where as you type an IP it suggests the DISTINCT possible next numbers in a drop-down DIV. So if I had: 192.168.12.[1 .. 254] 192.168.15.[1 .. 254] 192.168.158.[1 .. 254] 172.16.2.[1 .. 254] Then if I typed 1, the suggestions would be: 192.168.12. 192.168.15. 192.168.158. 172.16.2. Then if I typed a 9 next (so I have 19 in the box) the suggestions would be: 192.168.12. 192.168.15. 192.168.158. Skipping forward, if I had 192.168.15 in the box the suggestions should be: 192.168.15. 192.168.158. And so forth... Anyone have any hints or ideas as to how to formulate a SQL query or bunch of queries to get these 'lists' of results? I'd also be okay with it only working on quad boundaries (.) if that is substantially easier. I currently store IPs as INT values for obvious reasons, but there is the handy INET_NTOA(IP) as niceip so a HAVING clause should be able to use that I suspect. Thanks, Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need a query to show distinct IP dotted quad components [SOLVED]
I may have just solved my own problem: SELECT DISTINCT(SUBSTRING_INDEX(INET_NTOA(IP_Addr), '.', 3)) as niceip FROM IPTable HAVING niceip LIKE '192.168.15%'; http://dev.mysql.com/doc/refman/5.0/en/string-functions.html -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Friday, May 19, 2006 2:46 PM To: mysql@lists.mysql.com Subject: Need a query to show distinct IP dotted quad components I have a table of many IP addresses. I'm doing some PHP/JS/AJAX to populate a select box based upon what someone types in a search field. That works great, except that a user can spend a lot of time guessing as to what possible IPs exist. What I'd like to do now is one of those google suggestions thingys where as you type an IP it suggests the DISTINCT possible next numbers in a drop-down DIV. So if I had: 192.168.12.[1 .. 254] 192.168.15.[1 .. 254] 192.168.158.[1 .. 254] 172.16.2.[1 .. 254] Then if I typed 1, the suggestions would be: 192.168.12. 192.168.15. 192.168.158. 172.16.2. Then if I typed a 9 next (so I have 19 in the box) the suggestions would be: 192.168.12. 192.168.15. 192.168.158. Skipping forward, if I had 192.168.15 in the box the suggestions should be: 192.168.15. 192.168.158. And so forth... Anyone have any hints or ideas as to how to formulate a SQL query or bunch of queries to get these 'lists' of results? I'd also be okay with it only working on quad boundaries (.) if that is substantially easier. I currently store IPs as INT values for obvious reasons, but there is the handy INET_NTOA(IP) as niceip so a HAVING clause should be able to use that I suspect. Thanks, Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing DISTINCT searches
Several of my DISTINCT searches are frequently showing up in the slow query log. These queries use multiple table joins. Using EXPLAIN shows that the queries are using the appropriate keys, as far as I know. Are DISTINCT searches using multiple joins slow? TIA. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing DISTINCT searches
Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? R. -Original Message- From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 7:52 AM To: mysql@lists.mysql.com Subject: Optimizing DISTINCT searches Several of my DISTINCT searches are frequently showing up in the slow query log. These queries use multiple table joins. Using EXPLAIN shows that the queries are using the appropriate keys, as far as I know. Are DISTINCT searches using multiple joins slow? TIA. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Optimizing DISTINCT searches
On 2006-05-01 11:55 AM, Robert DiFalco [EMAIL PROTECTED] wrote: Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? Robert - Query: SELECT DISTINCT Project.Site_ID, Site, Status, Type FROM Project, Site WHERE Site.Site_ID = Project.Site_ID ORDER BY Site; Site is the site name, Status and Type contain additional information about the site, and Site_ID is the unique site id. The Project table contains among other things a list of sites where the projects are being done. The results of this query are supposed to be a non-duplicated list of sites that are associated with at least one project. As the number of projects and sites have increased, this query is now frequently in the slow query log. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing DISTINCT searches
Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. R. -Original Message- From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 11:05 AM To: mysql@lists.mysql.com Subject: FW: Optimizing DISTINCT searches On 2006-05-01 11:55 AM, Robert DiFalco [EMAIL PROTECTED] wrote: Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? Robert - Query: SELECT DISTINCT Project.Site_ID, Site, Status, Type FROM Project, Site WHERE Site.Site_ID = Project.Site_ID ORDER BY Site; Site is the site name, Status and Type contain additional information about the site, and Site_ID is the unique site id. The Project table contains among other things a list of sites where the projects are being done. The results of this query are supposed to be a non-duplicated list of sites that are associated with at least one project. As the number of projects and sites have increased, this query is now frequently in the slow query log. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing DISTINCT searches
On 2006-05-01 1:14 PM, Robert DiFalco [EMAIL PROTECTED] wrote: Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. Robert - Your query doesn't work - it finds ALL the rows in Project table and hence repeats the sites.. I do have an index on the Site table, it is the Site_ID. The Project.Site_ID is also indexed. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing DISTINCT searches
How about something like this? SELECT Site.Site_ID, Site, Status, Type FROM Site WHERE EXISTS( SELECT * FROM Project) ORDER BY Site; I'm assuming Site_ID is unique in the Site table? -Original Message- From: Stephen P. Fracek, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:27 PM To: mysql@lists.mysql.com Subject: Re: Optimizing DISTINCT searches On 2006-05-01 1:14 PM, Robert DiFalco [EMAIL PROTECTED] wrote: Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. Robert - Your query doesn't work - it finds ALL the rows in Project table and hence repeats the sites.. I do have an index on the Site table, it is the Site_ID. The Project.Site_ID is also indexed. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need for distinct sum
Many thanks for your reply Shawn - I have some comments below.
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various
essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following
might work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do
it.
All my queries are generated dynamically, and I want to avoid
generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change
the
DB schema if needs be.
Thanks,
Yasir
Based on database theory, your schema is correct (so long as each
spell only consumes up to 1 unit of essence). As you have discovered,
you are trying to take a second-level summary within a single
statement. While the COUNT() aggregate function has a DISTINCT
modifier none of the others do. That is why you posted.
One technique you could try is to create an intermediate pivot table.
That way you can know how much of each essence has been used in each
spell.
CREATE TEMPORARY TABLE tmpSpellSummary (PRIMARY KEY
(wizard_id,spell_id)) SELECT
wizard_id,
spell_id,
SUM(if(type='AIR',1,0)) air,
SUM(if(type='EARTH',1,0)) earth,
SUM(if(type='FIRE',1,0)) fire,
SUM(if(type='WATER',1,0)) water
FROM spellcast
GROUP BY wizard_id, spell_id
Now you can join this pivot table to your other query and you won't
have the duplication. You can also modify this by adding (after the
GROUP BY clause):
HAVING air0 and water0
to pick out just those spell_id's that used both air and water.
MySQL won't let me use HAVING here (because HAVING only works on columns
that are in the SELECT list) so this is what I did:
SELECT wizard.name,
SUM(spell.power)
FROM
Re: Need for distinct sum
Yasir Assam [EMAIL PROTECTED] wrote on 04/03/2006 11:09:01 PM:
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following might
work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do it.
All my queries are generated dynamically, and I want to avoid generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change the
DB schema if needs be.
Thanks,
Yasir
Based on database theory, your schema is correct (so long as each spell
only consumes up to 1 unit of essence). As you have discovered, you are
trying to take a second-level summary within a single statement. While the
COUNT() aggregate function has a DISTINCT modifier none of the others do.
That is why you posted.
One technique you could try is to create an intermediate pivot table. That
way you can know how much of each essence has been used in each spell.
CREATE TEMPORARY TABLE tmpSpellSummary (PRIMARY KEY (wizard_id,spell_id))
SELECT
wizard_id,
spell_id,
SUM(if(type='AIR',1,0)) air,
SUM(if(type='EARTH',1,0)) earth,
SUM(if(type='FIRE',1,0)) fire,
SUM(if(type='WATER',1,0)) water
FROM spellcast
GROUP BY wizard_id, spell_id
Now you can join this pivot table to your other query and you won't have
the duplication. You can also modify this by adding (after the GROUP BY
clause):
HAVING air0 and water0
to pick out just those spell_id's that used both air and water.
Another technique you could try (still assuming that using an essence only
consumes 1 unit of it) is to add an essence bitmap value to your spell
table
Need for distinct sum
Hello,
I need to be able to sum over distinct values but I can't seem to do it
unless I use sub-selects (which I want to avoid doing).
To see what I mean, I've constructed a toy DB:
DROP TABLE IF EXISTS spell;
CREATE TABLE spell (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
power FLOAT,
time_casted DATETIME
);
DROP TABLE IF EXISTS wizard;
CREATE TABLE wizard (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
age INT UNSIGNED,
name VARCHAR(255)
);
DROP TABLE IF EXISTS spellcast;
CREATE TABLE spellcast (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
type ENUM ('fire', 'air', 'water', 'earth'),
spell_id INT UNSIGNED,
wizard_id INT UNSIGNED
);
INSERT INTO wizard (name, age) VALUES
('alan', 20),
('bill', 23),
('chris', 21);
INSERT INTO spell (power, time_casted) VALUES
(400, '2006-02-02 12:00'),
(432, '2006-02-04 12:00'),
(123, '2006-02-03 12:00'),
(543, '2006-02-08 12:00'),
(320, '2006-02-01 12:00'),
(102, '2006-02-12 12:00'),
(732, '2006-02-14 12:00'),
(948, '2006-02-18 12:00'),
(932, '2006-02-21 12:00'),
(842, '2006-02-26 12:00');
INSERT INTO spellcast (type, spell_id, wizard_id) VALUES
('fire', 1, 1),
('air', 1, 1),
('water', 1, 1),
('earth', 2, 1),
('water', 2, 1),
('fire', 3, 1),
('water', 3, 1),
('water', 4, 1),
('fire', 4, 1),
('air', 5, 1),
('fire', 6, 1),
('water', 7, 1),
('water', 1, 2),
('fire', 1, 2),
('air', 2, 2),
('earth', 3, 2),
('water', 3, 2),
('earth', 4, 2),
('fire', 4, 2),
('air', 4, 2),
('water', 1, 3),
('earth', 1, 3),
('air', 1, 3),
('water', 5, 3),
('fire', 5, 3),
('earth', 5, 3),
('water', 6, 3),
('air', 7, 3);
A spell is an individual spell that's been cast. A spellcast is the
action of casting the spell by a particular wizard (or a group of
wizards). When casting a spell, a wizard can contribute various essenses
(fire, earth, air, water). So for example, Alan cast a spell (id=1) and
contributed three essences (fire, air water) - this means that there
are 3 spellcast rows for this contribution to this spell.
Let's say I want to find the total power of all the spells cast by each
wizard that involve fire air. At first I thought the following might work:
SELECT wizard.name,
SUM(spell.power)
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
GROUP BY wizard.id;
But this is wrong. The above query will count some spells more than
once, so the resulting sum is greater than it should be.
The only way I can think of doing this correctly is to use sub-queries:
SELECT DISTINCT wizard.name,
sub.s
FROM
(SELECT SUM(inner_sub.power) AS s,
inner_sub.wiz_id
FROM
(SELECT DISTINCT
spell.id,
spell.power,
wizard.id AS wiz_id
FROM spell,
spellcast,
wizard
WHERE wizard.id = spellcast.wizard_id AND
spellcast.spell_id = spell.id AND
spellcast.type IN ('fire', 'air')
) AS inner_sub
GROUP BY inner_sub.wiz_id
) AS sub,
wizard,
spellcast
WHERE wizard.id = sub.wiz_id AND
spellcast.wizard_id = wizard.id AND
spellcast.type IN ('fire', 'air');
This works but I was wondering whether there was a simpler way to do it.
All my queries are generated dynamically, and I want to avoid generating
complex subqueries.
Anyone know of a way to do the above a lot more simply? I can change the
DB schema if needs be.
Thanks,
Yasir
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT Optimizations
SELECT DISTINCT can be kind of slow if there are many result values, specifically if those result values include large VARCHARs. Furthermore, some database engines cannot support a SELECT DISTINCT if any LOBs are included in the result values. I'm trying to find a general way to optimize SELECT DISTINCT since for more purposes I rarely need to decide DISTINCT on any more than a single column. For example, consider I have a tree structure where leafs or groups can have more than a single parent group (i.e. they are links). (A) SELECT DISTINCT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Something like this can be slow on a large result set with many large character columns and it does not even express clearly what I mean, because what I really mean to say is: (B) SELECT ( FOR DISTINCT ON G.oid ), G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Of course there is no valid SQL like this. So what I end up doing is the following: (C) SELECT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G WHERE G.oid IN ( SELECT G.oid FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345)) For the majority of cases this will perform better than (A). However, sometimes the expense of the subquery will out weight the sort used for the distinct and it will not be faster. Is there a standard SQL way to avoid sorting on every field for a distinct on a unique key without having to perform a subquery? Maybe something with a group by? TIA for any ideas or thoughts... R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT uses index but is still slow
0.01 seconds is so fast that I wonder if that's actually because the query cache is storing the query. Do you have query cache enabled? James At 6:35 am + 5/1/06, C.R.Vegelin wrote: Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query Select myKey, count(*) from myTable Group By myKey; takes with the CLI about 25 seconds, BUT the second time it takes only 0.01 second ! I think that the 1st query run includes loading indices into memory. I suggest to test your query twice from the CLI. HTH, Cor Vegelin I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT uses index but is still slow
I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT uses index but is still slow
Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query Select myKey, count(*) from myTable Group By myKey; takes with the CLI about 25 seconds, BUT the second time it takes only 0.01 second ! I think that the 1st query run includes loading indices into memory. I suggest to test your query twice from the CLI. HTH, Cor Vegelin - Original Message - From: James Harvard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 8:28 PM Subject: SELECT DISTINCT uses index but is still slow I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can conditions be mixed with DISTINCT()
Hi, I think you could use something like SELECT DISTINCT (CASE p.ship_status WHEN '1' THEN shipping_now WHEN '2' THEN shipping_soon ELSE 'unknow' END) as status FROM products AS p INNER JOIN cart AS i ON i.product_id = p.id WHERE i.session_id = prepSQL(tConn, tSessionID); If there are any products for the selected session_id this should return one row for every status like this | status | +-+ | unknown | | shipping_now | | shipping_soon | HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Tuesday 25 October 2005 01:00, Scott Haneda wrote: tSql = SELECT DISTINCT(p.ship_status) FROM products AS p INNER JOIN cart AS i ON i.product_id = p.id WHERE i.session_id = prepSQL(tConn, tSessionID); p.ship_status is either a 1 or a 0, which is just how the database was set up ages, ago. I am moving these to enum() types as I go, but to change this one, would break too much stuff. I would like to toss in a condition to the select so it returns shipping_now for 1 and shipping_soon for 2. When I do this, I get zero results returned. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can conditions be mixed with DISTINCT()
tSql = SELECT DISTINCT(p.ship_status) FROM products AS p INNER JOIN cart AS i ON i.product_id = p.id WHERE i.session_id = prepSQL(tConn, tSessionID); p.ship_status is either a 1 or a 0, which is just how the database was set up ages, ago. I am moving these to enum() types as I go, but to change this one, would break too much stuff. I would like to toss in a condition to the select so it returns shipping_now for 1 and shipping_soon for 2. When I do this, I get zero results returned. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting a group of distinct items?
Hi, I'm pretty much an SQL newbie, so apologies in advance if this is basic stuff. That'll teach me for skipping the databases class in college... I just created an app using a table that has entries like this simplified version: name datevalue Fred 2005-10-01 7 Fred 2005-10-02 10 Joe2005-10-01 4 Joe2005-10-01 10 and so on. New values get inserted every day, but old ones remain. I'd like a query that selects the value field for the most recent date for each name. Since I didn't know how, right now I'm selecting all the distinct names and looping over those in PHP to do queries that grab the latest value from each. But next I want to be able to sort that overall list by value, which just leads to more and more complexity with my workaround. If all else fails, I suppose I can have that loop create a temporary table with each name and then sort that, but it seems like there has to be a cleaner solution out there. Is there? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
