How to find top 25 selling products for each day of year?
I have a table (MyISAM) with summarized Sales data: Table: ProdSales Columns: Sales_Date Date, Product_Code Char(10), Amt_Sold Double There are approx 5,000 products sold each day and there are 3 years worth of data. I would like to create a table with the top 25 Amt_Sold products for each day Example: '2011-03-01', ABC001, 30421.21 '2011-03-01', ABC031, 3.15 '2011-03-01', ABC011, 23312.00 '2011-03-01', ABC101, 22211.87 '2011-03-01', DE0211, 21931.44 '2011-03-01', AGC331, 20321.32 '2011-03-01', DEF321, 20300.31 '2011-03-01', KLC031, 2.21 '2011-03-01', MIU031, 19332.00 25th top Amt_Sold for 2011-03-11 '2011-03-02', FER001, 40421.21 '2011-03-02', DEC031, 40010.15 etc.. So the table would have 25 rows per date and there would be several years worth of data. Now I can do this easy enough for one date. But is there an efficient way of doing this for each day of the year without resorting to executing the same SQL statement for each day? TIA Mike (MySQL 5.5) -- 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 find top 25 selling products for each day of year?
I would like to create a table with the top 25 Amt_Sold products for each day Examples at Top N per group at http://www.artfulsoftware.com/queries.php PB - On 5/8/2011 12:21 PM, mos wrote: I have a table (MyISAM) with summarized Sales data: Table: ProdSales Columns: Sales_Date Date, Product_Code Char(10), Amt_Sold Double There are approx 5,000 products sold each day and there are 3 years worth of data. I would like to create a table with the top 25 Amt_Sold products for each day Example: '2011-03-01', ABC001, 30421.21 '2011-03-01', ABC031, 3.15 '2011-03-01', ABC011, 23312.00 '2011-03-01', ABC101, 22211.87 '2011-03-01', DE0211, 21931.44 '2011-03-01', AGC331, 20321.32 '2011-03-01', DEF321, 20300.31 '2011-03-01', KLC031, 2.21 '2011-03-01', MIU031, 19332.00 25th top Amt_Sold for 2011-03-11 '2011-03-02', FER001, 40421.21 '2011-03-02', DEC031, 40010.15 etc.. So the table would have 25 rows per date and there would be several years worth of data. Now I can do this easy enough for one date. But is there an efficient way of doing this for each day of the year without resorting to executing the same SQL statement for each day? TIA Mike (MySQL 5.5) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SUM Top 10 records
Hi I've a basic table like and want to SUM the top 5 values. For example if I have id, rating 0, 10 1, 25 2, 5 3, 10 4, 50 5, 1 6, 15 7, 20 8, 9 I want my query to sum the values 4,50 1,25 7,20 6.15 0,10 Suming a value of 120 Any suggestions on how to achieve this ? Cheers Neil
Re: SUM Top 10 records
Christoph, this SUMs all values ? On Tue, Sep 28, 2010 at 11:25 AM, Christoph Boget christoph.bo...@gmail.com wrote: I've a basic table like and want to SUM the top 5 values. For example if I have Any suggestions on how to achieve this ? SELECT SUM( rating ) as total_rating FROM my-table ORDER BY rating DESC LIMIT 5 IIRC, that should work thnx, Christoph
Re: SUM Top 10 records
select `rating`/100+`id` as result from `your_table_name` order by `rating` desc LIMIT 5; ++ | result | ++ | 4.5000 | | 1.2500 | | 7.2000 | | 6.1500 | | 0.1000 | ++ 2010/9/28 Tompkins Neil neil.tompk...@googlemail.com Hi I've a basic table like and want to SUM the top 5 values. For example if I have id, rating 0, 10 1, 25 2, 5 3, 10 4, 50 5, 1 6, 15 7, 20 8, 9 I want my query to sum the values 4,50 1,25 7,20 6.15 0,10 Suming a value of 120 Any suggestions on how to achieve this ? Cheers Neil -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
Re: SUM Top 10 records
I'm sorry! SELECT sum(rating) FROM (SELECT rating FROM your_table_name ORDER BY rating DESC LIMIT 5) AS result; 2010/9/28 Евгений Килимчук ekilimc...@gmail.com select `rating`/100+`id` as result from `your_table_name` order by `rating` desc LIMIT 5; ++ | result | ++ | 4.5000 | | 1.2500 | | 7.2000 | | 6.1500 | | 0.1000 | ++ 2010/9/28 Tompkins Neil neil.tompk...@googlemail.com Hi I've a basic table like and want to SUM the top 5 values. For example if I have id, rating 0, 10 1, 25 2, 5 3, 10 4, 50 5, 1 6, 15 7, 20 8, 9 I want my query to sum the values 4,50 1,25 7,20 6.15 0,10 Suming a value of 120 Any suggestions on how to achieve this ? Cheers Neil -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com -- Best regards, Eugene Kilimchuk ekilimc...@gmail.com
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
So with skip-name-resolve in my.cnf (and MySQL restarted), it should be okay to have [EMAIL PROTECTED] in the GRANT table since localhost resolves without DNS lookup? Or do I need to specify [EMAIL PROTECTED] Sorry, just need to clarify this bit before changing a few things. Thanks again. ...Rene On 24-Sep-08, at 3:33 PM, Ken Menzel wrote: Yes, you can still use a hostname in the connection string, that's not what mysql uses it for, that hostname gets you from the client to the server. If you use GRANT to permit access from certain hosts IE 'grant all on mydb.* to 'mydbuuser'@'%.mydomain.com'. Then the server will not be able to resolve those named permissions from the connecting IP back to a domain name matched to a grant to allow login. Also I don't think you can use subnets in a grant, you can use a single IP. However 'myuser'@'%' should continue to work just fine as should localhost using the file socket. Everything else should behave normally. Localhost connections are usually through the the mysql socket file (/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Presently, I'm only using localhost for MySQL database user privileges, e.g., : [EMAIL PROTECTED] -- for all privileges on all databases Do I need to change the above if I add skip-name-resolve to my.cnf? (Since localhost is, I thought, not really part of DNS but just an alias for 127.0.0.1 -- so there's no lookup needed, right?) If I do need to change it, would it be to add [EMAIL PROTECTED] ? ...Rene On 24-Sep-08, at 3:59 PM, Martin Gainty wrote: Hello Rene is hostname a FQDN or IP? Martin
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
from http://dev.mysql.com/doc/refman/5.1/en/connecting.html On Unix, MySQL programs treat the hostname |localhost| specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to |localhost|, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a |--port| or |-P| option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use |--host| or |-h| to specify a hostname value of |127.0.0.1|, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for |localhost|, by using the |--protocol=TCP| option. For example: shell *|mysql --host=127.0.0.1|* shell *|mysql --protocol=TCP |* *|Just try it. localhost is trated as a special name used for the socketfile. If you want to use 127.0.0.1 or have programs that use that then you will need to specify 127.0.0.1 when using skip-dns option. It does not hurt to grant both localhost and 127.0.0.1 if you are unsure when you are not using DNS. Ken |**||* *||* Rene Fournier wrote: So with skip-name-resolve in my.cnf (and MySQL restarted), it should be okay to have [EMAIL PROTECTED] in the GRANT table since localhost resolves without DNS lookup? Or do I need to specify [EMAIL PROTECTED] Sorry, just need to clarify this bit before changing a few things. Thanks again. ...Rene On 24-Sep-08, at 3:33 PM, Ken Menzel wrote: Yes, you can still use a hostname in the connection string, that's not what mysql uses it for, that hostname gets you from the client to the server. If you use GRANT to permit access from certain hosts IE 'grant all on mydb.* to 'mydbuuser'@'%.mydomain.com'. Then the server will not be able to resolve those named permissions from the connecting IP back to a domain name matched to a grant to allow login. Also I don't think you can use subnets in a grant, you can use a single IP. However 'myuser'@'%' should continue to work just fine as should localhost using the file socket. Everything else should behave normally. Localhost connections are usually through the the mysql socket file (/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost.
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Thanks. I've read those links, and they sound like my problem. On each connection, MySQL calls gethostbyname() to resolve the hostname in the connection string into 127.0.0.1 -- e.g., mysql_connect(localhost, user, password) - 127.0.0.1. Because FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe, bad things can happen while MySQL waits on gethostbyname(). At least, that's where the CPU is spending much of its time. Now, it sounds like using using 127.0.0.1 in place of localhost in the connection string is not enough, since MySQL will still call gethostbyaddr() as a reverse-lookup. (Right?) So this is why, as you say, it's necessary to add skip-name-resolve to my.cnf. (Right?) It's also then necessary to make the Grant tables not depend on hostnames (localhost), but specify 127.0.0.1. But here's the strange thing: On a test machine, I've added skip-name- resolve to my.cnf. But I can still use a hostname in the connection string, and it works. On 23-Sep-08, at 5:44 PM, Ken Menzel wrote: Hi Rene, This smells like an old freebsd issue with a non thread safe get- host-by-name issue and possibly other thread issues. Since Mac OS/X/ Darwin is a freebsd 4 branch it is a good bet they are the same. Is it possible for you to try adding skip-name-resolve to my.cnf. Alternatively you could compile with -D SKIP_DNS_CHECK. Please read about these options before trying them to understand any implication it my have on your GRANTs if you grant to a domain or server. Here are some links to more information, http://jeremy.zawodny.com/blog/archives/000203.html http://bugs.mysql.com/bug.php?id=414 http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html Hope this helps, Ken Rene Fournier wrote: In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/ MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30
RE: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Hello Rene is hostname a FQDN or IP? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. CC: [EMAIL PROTECTED]; mysql@lists.mysql.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps Date: Wed, 24 Sep 2008 14:21:44 +0200 Thanks. I've read those links, and they sound like my problem. On each connection, MySQL calls gethostbyname() to resolve the hostname in the connection string into 127.0.0.1 -- e.g., mysql_connect(localhost, user, password) - 127.0.0.1. Because FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe, bad things can happen while MySQL waits on gethostbyname(). At least, that's where the CPU is spending much of its time. Now, it sounds like using using 127.0.0.1 in place of localhost in the connection string is not enough, since MySQL will still call gethostbyaddr() as a reverse-lookup. (Right?) So this is why, as you say, it's necessary to add skip-name-resolve to my.cnf. (Right?) It's also then necessary to make the Grant tables not depend on hostnames (localhost), but specify 127.0.0.1. But here's the strange thing: On a test machine, I've added skip-name- resolve to my.cnf. But I can still use a hostname in the connection string, and it works. On 23-Sep-08, at 5:44 PM, Ken Menzel wrote: Hi Rene, This smells like an old freebsd issue with a non thread safe get- host-by-name issue and possibly other thread issues. Since Mac OS/X/ Darwin is a freebsd 4 branch it is a good bet they are the same. Is it possible for you to try adding skip-name-resolve to my.cnf. Alternatively you could compile with -D SKIP_DNS_CHECK. Please read about these options before trying them to understand any implication it my have on your GRANTs if you grant to a domain or server. Here are some links to more information, http://jeremy.zawodny.com/blog/archives/000203.html http://bugs.mysql.com/bug.php?id=414 http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html Hope this helps, Ken Rene Fournier wrote: In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/ MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Hi Rene, (Note I have updated my reply address to my current company name). Yes, you can still use a hostname in the connection string, that's not what mysql uses it for, that hostname gets you from the client to the server. If you use GRANT to permit access from certain hosts IE 'grant all on mydb.* to 'mydbuuser'@'%.mydomain.com'. Then the server will not be able to resolve those named permissions from the connecting IP back to a domain name matched to a grant to allow login. Also I don't think you can use subnets in a grant, you can use a single IP. However 'myuser'@'%' should continue to work just fine as should localhost using the file socket. Everything else should behave normally. Localhost connections are usually through the the mysql socket file (/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost. Don't forget to restart the server after the change in the my.cnf file. Hopefully Apple will rebranch Darwin from a more current freebsd, there have been many improvements in threading, SMP scheduling and performance, and I highly recommend version 7 and 8 looks like it will be even better. Ken Rene Fournier wrote: Thanks. I've read those links, and they sound like my problem. On each connection, MySQL calls gethostbyname() to resolve the hostname in the connection string into 127.0.0.1 -- e.g., mysql_connect(localhost, user, password) - 127.0.0.1. Because FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe, bad things can happen while MySQL waits on gethostbyname(). At least, that's where the CPU is spending much of its time. Now, it sounds like using using 127.0.0.1 in place of localhost in the connection string is not enough, since MySQL will still call gethostbyaddr() as a reverse-lookup. (Right?) So this is why, as you say, it's necessary to add skip-name-resolve to my.cnf. (Right?) It's also then necessary to make the Grant tables not depend on hostnames (localhost), but specify 127.0.0.1. But here's the strange thing: On a test machine, I've added skip-name-resolve to my.cnf. But I can still use a hostname in the connection string, and it works. On 23-Sep-08, at 5:44 PM, Ken Menzel wrote: Hi Rene, This smells like an old freebsd issue with a non thread safe get-host-by-name issue and possibly other thread issues. Since Mac OS/X/Darwin is a freebsd 4 branch it is a good bet they are the same. Is it possible for you to try adding skip-name-resolve to my.cnf. Alternatively you could compile with -D SKIP_DNS_CHECK. Please read about these options before trying them to understand any implication it my have on your GRANTs if you grant to a domain or server. Here are some links to more information, http://jeremy.zawodny.com/blog/archives/000203.html http://bugs.mysql.com/bug.php?id=414 http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html Hope this helps, Ken Rene Fournier wrote: In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/ MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Hi Rene, This smells like an old freebsd issue with a non thread safe get-host-by-name issue and possibly other thread issues. Since Mac OS/X/Darwin is a freebsd 4 branch it is a good bet they are the same. Is it possible for you to try adding skip-name-resolve to my.cnf. Alternatively you could compile with -D SKIP_DNS_CHECK. Please read about these options before trying them to understand any implication it my have on your GRANTs if you grant to a domain or server. Here are some links to more information, http://jeremy.zawodny.com/blog/archives/000203.html http://bugs.mysql.com/bug.php?id=414 http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html Hope this helps, Ken Rene Fournier wrote: In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange
force row to appear at top of results using order by
Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? Many thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: force row to appear at top of results using order by
Many thanks for the quick replies! This solution appears the most elegant: -- Forwarded message -- From: Markus Grossrieder [EMAIL PROTECTED] Date: 2008/7/1 Subject: Re: force row to appear at top of results using order by To: Andrew Martin [EMAIL PROTECTED] Andrew, something like this (air code) SELECT events_groups_id, events_groups_name, IF(events_groups_name='Personal Events', 1, 2) as sort_elem FROM events_groups; ORDER BY sort_elem, events_groups_name; Regards, Markus - Original Message - From: Andrew Martin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, July 01, 2008 3:19 PM Subject: force row to appear at top of results using order by Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? Many thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.3/1528 - Release Date: 7/1/2008 7:26 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: force row to appear at top of results using order by
SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY IF(events_groups_id=1,0,1),events_groups_name ASC; -Original Message- From: Andrew Martin [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 9:20 AM To: mysql@lists.mysql.com Subject: force row to appear at top of results using order by Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? Many thanks, Andy -- 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: force row to appear at top of results using order by
-Original Message- From: Andrew Martin [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2008 9:20 AM To: mysql@lists.mysql.com Subject: force row to appear at top of results using order by Hello, I have an order by question... This is the raw data... mysql SELECT events_groups_id, events_groups_name FROM events_groups; +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |4 | Invoices to pay | |5 | Invoices to receive | |6 | Deliveries out | |9 | Online demos| +--+-+ 7 rows in set (0.01 sec) This is almost the result I require: mysql SELECT events_groups_id, events_groups_name FROM events_groups ORDER BY events_groups_name ASC; +--+-+ | events_groups_id | events_groups_name | +--+-+ |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| |1 | Personal Events | +--+-+ 7 rows in set (0.00 sec) I would like this to have Personal Events appear first, and the rest of the list still be alphabetically sorted like so: +--+-+ | events_groups_id | events_groups_name | +--+-+ |1 | Personal Events | |2 | Company events | |3 | Deliveries in | |6 | Deliveries out | |4 | Invoices to pay | |5 | Invoices to receive | |9 | Online demos| +--+-+ Is this possible without using a subquery or union? [JS] IMHO, you are better off approaching this in a different way. Add a third column that is used only as a sort key, so that you can rearrange your sort order whenever you want. I would use a sort key that has two decimal places, so that you can easily add an entry that needs to be sorted between 1.00 and 2.00 just by giving it a sort key of 1.50. This will give you complete flexibility, and should use minimal overhead. Many thanks, Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find the top most member in a hierarchy of subcategories
I found something that we can get all the tree. SELECT cat_id, group_concat( id ) FROM categoria GROUP BY cat_id Try it and tell me if it´s good or not. abhishek jain [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Hi, I have a table structure like : ID , NAME, PARENT_ID 1 , Top , 0 2 , Level 1 , 1 3 , Level 2 ,2 4 , Another Top , 0 and so on. I wanted to know the topmost cat. if i have the lowest category id ie. 3 in this case. I wanted to get like 3-2-1 Pl. help me , cn i do this in one query, also i do not know how many sublevels are there, Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to find the top most member in a hierarchy of subcategories
Hi, I have a table structure like : ID , NAME, PARENT_ID 1 , Top , 0 2 , Level 1 , 1 3 , Level 2 ,2 4 , Another Top , 0 and so on. I wanted to know the topmost cat. if i have the lowest category id ie. 3 in this case. I wanted to get like 3-2-1 Pl. help me , cn i do this in one query, also i do not know how many sublevels are there, Thanks, Abhishek jain
Re: How to find the top most member in a hierarchy of subcategories
I´m not quite sure if it could help you, because it´s whole in portuguese,
but i´ll send you.
My table is called categoria and has the follow structure:
CREATE TABLE `categoria` (
`id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY (`id`)
)
Where the relations each register of the tree are like this:
ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
And then i use the follow routine to get the tree:
$sql = SELECT
*
FROM
categoria
ORDER BY
cat_id, nome;
$con-Query($sql);
$counter = $con-count;
$categorias=array();
for($z=0;$z$con-count;$z++){
$con-Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con-result;
$categorias[$id_cat]=array(id_cat = $id_cat, cat_id = $cat_id,
nome_cat = $nome_cat, icone_cat = $icone_cat, familia = $id_cat, );
$continua=$cat_id!=null $cat_id0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat][indice]=;
while ($continua) {
$categorias[$id_cat][indice]=$categorias[$qual][nome_cat].$categorias[$id_cat][indice];
$continua=$categorias[$qual][cat_id]!=null
$categorias[$qual][cat_id]0;
$tem_pai=$cat_id!=null $cat_id0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai][familia],,
.$categorias[$id_cat][id_cat]))
$categorias[$pai][familia].=, .$categorias[$id_cat][id_cat];
$tem_pai=$categorias[$pai][cat_id]!=null
$categorias[$pai][cat_id]0;
$pai=$categorias[$pai][cat_id];
}
if ($continua) {
$qual=$categorias[$qual][cat_id];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria[indice].$categoria[nome_cat]]=array(id_cat =
$categoria[id_cat], nome_cat = $categoria[nome_cat], icone_cat =
$categoria[icone_cat], indice = $categoria[indice], familia =
$categoria[familia]);
}
ksort($linhas);
reset($linhas);
Hope help you.
--
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br
abhishek jain [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3
in
this case.
I wanted to get like 3-2-1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to find the top most member in a hierarchy of subcategories
MySQL is not recursive. This might help you: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html /Peter www.lauri.se - personal web site www.dwsasia.com - corporate web site -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of abhishek jain Sent: Monday, September 04, 2006 4:29 PM To: mysql@lists.mysql.com Subject: How to find the top most member in a hierarchy of subcategories Hi, I have a table structure like : ID , NAME, PARENT_ID 1 , Top , 0 2 , Level 1 , 1 3 , Level 2 ,2 4 , Another Top , 0 and so on. I wanted to know the topmost cat. if i have the lowest category id ie. 3 in this case. I wanted to get like 3-2-1 Pl. help me , cn i do this in one query, also i do not know how many sublevels are there, Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to find the top most member in a hierarchy of subcategories
Yes, and this shows that you can not do it will MySQL purely :) But a
scripting language like php can do it for you with a recursive function as
the best option.
/Peter
-Original Message-
From: João Cândido de Souza Neto [mailto:[EMAIL PROTECTED]
Sent: Monday, September 04, 2006 7:55 PM
To: mysql@lists.mysql.com
Subject: Re: How to find the top most member in a hierarchy of subcategories
I´m not quite sure if it could help you, because it´s whole in portuguese,
but i´ll send you.
My table is called categoria and has the follow structure:
CREATE TABLE `categoria` (
`id` int(20) NOT NULL auto_increment,
`cat_id` int(20) NOT NULL default '0',
`nome` varchar(50) NOT NULL default '',
`icone` varchar(255) NOT NULL default '',
`cod_shop` varchar(5) NOT NULL default '',
`topo` char(1) NOT NULL default '',
PRIMARY KEY (`id`)
)
Where the relations each register of the tree are like this:
ID , CATEGORIA, CAT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
And then i use the follow routine to get the tree:
$sql = SELECT
*
FROM
categoria
ORDER BY
cat_id, nome;
$con-Query($sql);
$counter = $con-count;
$categorias=array();
for($z=0;$z$con-count;$z++){
$con-Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con-result;
$categorias[$id_cat]=array(id_cat = $id_cat, cat_id = $cat_id,
nome_cat = $nome_cat, icone_cat = $icone_cat, familia = $id_cat, );
$continua=$cat_id!=null $cat_id0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat][indice]=;
while ($continua) {
$categorias[$id_cat][indice]=$categorias[$qual][nome_cat].$categorias[$i
d_cat][indice];
$continua=$categorias[$qual][cat_id]!=null
$categorias[$qual][cat_id]0;
$tem_pai=$cat_id!=null $cat_id0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai][familia],,
.$categorias[$id_cat][id_cat]))
$categorias[$pai][familia].=, .$categorias[$id_cat][id_cat];
$tem_pai=$categorias[$pai][cat_id]!=null
$categorias[$pai][cat_id]0;
$pai=$categorias[$pai][cat_id];
}
if ($continua) {
$qual=$categorias[$qual][cat_id];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria[indice].$categoria[nome_cat]]=array(id_cat =
$categoria[id_cat], nome_cat = $categoria[nome_cat], icone_cat =
$categoria[icone_cat], indice = $categoria[indice], familia =
$categoria[familia]);
}
ksort($linhas);
reset($linhas);
Hope help you.
--
João Cândido de Souza Neto
Curitiba Online
[EMAIL PROTECTED]
(41) 3324-2294 (41) 9985-6894
http://www.curitibaonline.com.br
abhishek jain [EMAIL PROTECTED] escreveu na mensagem
news:[EMAIL PROTECTED]
Hi,
I have a table structure like :
ID , NAME, PARENT_ID
1 , Top , 0
2 , Level 1 , 1
3 , Level 2 ,2
4 , Another Top , 0
and so on.
I wanted to know the topmost cat. if i have the lowest category id ie. 3
in
this case.
I wanted to get like 3-2-1
Pl. help me , cn i do this in one query, also i do not know how many
sublevels are there,
Thanks,
Abhishek jain
--
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: How to find the top most member in a hierarchy of subcategories
Peter Lauri wrote: Yes, and this shows that you can not do it will MySQL purely :) But a scripting language like php can do it for you with a recursive function as the best option. IMHO, the best option would do it with a procedure as you don't get out of the database and don't have any overhead from outside. --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MS SQL TOP Replacement
I have a SELECT statement for MS SQL that is SELECT TOP 1 id FROM menu_links ORDER BY id desc and need to convert it to MySQL. I cannot find what will do that. Help is greatly appreciated. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS SQL TOP Replacement
Mike wrote: I have a SELECT statement for MS SQL that is SELECT TOP 1 id FROM menu_links ORDER BY id desc and need to convert it to MySQL. I cannot find what will do that. Help is greatly appreciated. TIA Mike SELECT id FROM menu_links ORDER BY id desc LIMIT 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MS SQL TOP Replacement
SELECT max(id) FROM menu_links :) -Original Message- From: Mike Wexler [mailto:[EMAIL PROTECTED] Sent: Thursday, June 29, 2006 1:22 PM To: Mike Cc: mysql@lists.mysql.com Subject: Re: MS SQL TOP Replacement Mike wrote: I have a SELECT statement for MS SQL that is SELECT TOP 1 id FROM menu_links ORDER BY id desc and need to convert it to MySQL. I cannot find what will do that. Help is greatly appreciated. TIA Mike SELECT id FROM menu_links ORDER BY id desc LIMIT 1 -- 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: MS SQL TOP Replacement
I guess I had the LIMIT in the wrong location. Thanks for the help! Mike Mike Wexler [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Mike wrote: I have a SELECT statement for MS SQL that is SELECT TOP 1 id FROM menu_links ORDER BY id desc and need to convert it to MySQL. I cannot find what will do that. Help is greatly appreciated. TIA Mike SELECT id FROM menu_links ORDER BY id desc LIMIT 1 -- 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]
World-Wide Attention: Top Language to interface with SQL servers - PostgreSQL, MySQL, FireBird
World-Wide public announcement (every human animal on this planet must read this announcement) : The top RDBMS SQL systems are: Number one: PostgreSQL Number two: FireBird and MySQL. Now, which are the top computer languages which you would use to interface with these SQL servers?? You MUST write your apps in pure object oriented language like - SmartEiffel, OCAML (Camel), Ruby, D-language or Java. I found the best ones to be : Number One: SmartEiffel http://smarteiffel.loria.fr Number Two: Camel (OCAML) http://caml.inria.fr Number Three: Ruby (With ruby virtual machine to speed up) http://www.ruby-lang.org/en Number Four: D-language and Java. http://www.digitalmars.com/d/dcompiler.html All the Banks, Financial Institutions, Governments, Universities, Military, Corporations in the whole world (every country) must take this advice in this announcement. SmartEiffel is found to be very interesting because it's compiler generates C language code output! In fact, 3 of the 4 Eiffel compilers actually output C code! And you know that C is the most portable language. Strongly recommended is the SmartEiffel for all your applications. The output C code from Eiffel is compiled. Also SmartEiffel can output to Java JVM bytecodes. See also language shootout at : http://www.geocities.com/alavoor The advice here is multi-billion dollar advice (take it serious)! Al Dev __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Top N selections + rest row
Hi All, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. Regards, Cor
Re: Top N selections + rest row
C.R.Vegelin wrote: Hi All, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. Regards, Cor Writing it into a cache_table. Write the first rows into the cache_table and then the rest of it. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- 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: Top N selections + rest row
--- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html That sounds like what he needs, excellent :-) Martijn Tonies Database Workbench - development tool for Firebird and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Shawn, Cor, all! Shawn Green wrote: --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html I checked it here: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html It seems that WITH ROLLUP is not adequate for Cor's needs, see this quote: | LIMIT can be used to restrict the number of rows returned to the | client. LIMIT is applied after ROLLUP, so the limit applies against | the extra rows added by ROLLUP. Cor, what about a UNION? Untested: ( Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25 ) UNION ( SELECT World, Sum(Sales) AS Sales From myTable Where Year=2005 ) ; Note the extra parentheses, according to the manual they are needed to ensure that the limit is applied to the first select only. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Thanks Shawn, According: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html: When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. ... Regards, Cor - Original Message - From: Shawn Green [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED]; Martijn Tonies [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED] Sent: Tuesday, April 25, 2006 1:18 PM Subject: Re: Top N selections + rest row --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 11:06 AM Subject: Re: Top N selections + rest row Hi, Anybody with smart ideas to get Top-N rows plus the rest row ? Suppose I have ranked sales values descending for 2005, like: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25; Then I get Top-25 sales rows, but I also want a rest row, where all 26 rows represent the world total. I'm using MySQL 5.0.15. What about just removing the LIMIT clause? Or, alternatively, do a skip of the first 25 rows? (check the docs for that) Do you want to get this in a single result, or additional result? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html Shawn Green __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Joerg, All, I would like to have something like: Country Type20042005 -- GermanySales13357 19843 Belgium Sales12224 16767 France Sales15443 16602 Un. States Sales11995 14332 Japan Sales14234 13364 Rest Sales17663 12563 -- if a user requires a Top-5 selection for 2005, and where Totals of both 2004 and 2005 are 100%. Regards, Cor - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] To: Shawn Green [EMAIL PROTECTED]; C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 2:17 PM Subject: Re: Top N selections + rest row Hi Shawn, Cor, all! Shawn Green wrote: --- C.R.Vegelin [EMAIL PROTECTED] wrote: Thanks Martijn, Barry, I was wondering whether it could be done in a single query. I want users to decide how many countries they want, and show world sales on top of report followed by the N countries. This to enable relative country shares, both for reporting and graphs. For example, Top-10 countries + Rest in a pie graph. So I need one additional row in the Top-N query. Regards, Cor Have you considered using the WITH ROLLUP modifier? Select Country , Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country WITH ROLLUP Order By Sales DESC LIMIT 25; http://dev.mysql.com/doc/refman/5.0/en/select.html I checked it here: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html It seems that WITH ROLLUP is not adequate for Cor's needs, see this quote: | LIMIT can be used to restrict the number of rows returned to the | client. LIMIT is applied after ROLLUP, so the limit applies against | the extra rows added by ROLLUP. Cor, what about a UNION? Untested: ( Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC LIMIT 25 ) UNION ( SELECT World, Sum(Sales) AS Sales From myTable Where Year=2005 ) ; Note the extra parentheses, according to the manual they are needed to ensure that the limit is applied to the first select only. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Top N selections + rest row
Hi Cor, all! C.R.Vegelin wrote: Hi Joerg, All, I would like to have something like: Country Type20042005 -- GermanySales13357 19843 Belgium Sales12224 16767 France Sales15443 16602 Un. States Sales11995 14332 Japan Sales14234 13364 Rest Sales17663 12563 -- if a user requires a Top-5 selection for 2005, and where Totals of both 2004 and 2005 are 100%. My impression is your requirements are slowly changing: - Originally, I read world, now I read rest. The problem IMO is that rest is difficult to compute, because you need to sum on a set difference (all rows minus the top N rows) which you can only build by first determining the top N. - You started asking for 2005, now show 2005 + 2004 (minor). If you really want top N, and the sum of all others, IMHO the best way is to do both the limitation and the summing of the other rows in your application: Select Country, Sum(Sales) AS Sales From myTable Where Year=2005 Group By Country Order By Sales DESC Loop over the results, display the first N, do the summing for all others, display that sum. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
top one row
I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: top one row
You're looking for the rows containing the group-wise maximum. There's an explanation in the manual, section 3.6.4, http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html On 8/5/05, Kemin Zhou [EMAIL PROTECTED] wrote: I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- 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: top one row
[snip] I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? [/snip] The same way you do it inother SQL's. SELECT MAX(col2) FROM table GROUP BY col1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
[snip] The same way you do it inother SQL's. SELECT MAX(col2) FROM table GROUP BY col1; [/snip] Oops; SELECT col1, MAX(col2), col3 FROM table GROUP BY col1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: top one row
SELECT col1, MAX(col2), col3 FROM table GROUP BY col1; Because col3 is not part of the GROUP BY clause, this query will not guarantee that the col3 returned is associated with the col2 returned. In other words, running this query on the table containing col1 / col2 / col3 1 11 1 23 may return 1 21 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
[snip] SELECT col1, MAX(col2), col3 FROM table GROUP BY col1; Because col3 is not part of the GROUP BY clause, this query will not guarantee that the col3 returned is associated with the col2 returned. In other words, running this query on the table containing col1 / col2 / col3 1 11 1 23 may return 1 21 [/snip] I have tested this several times and never got those kind of results, do you have some docs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: top one row
mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.7-nt | +---+ 1 row in set (0.00 sec) mysql CREATE TABLE test (col1 INT, col2 INT, col3 INT); Query OK, 0 rows affected (0.08 sec) mysql INSERT INTO test VALUES (1, 1, 1), (1, 2, 3); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT * FROM test; +--+--+--+ | col1 | col2 | col3 | +--+--+--+ |1 |1 |1 | |1 |2 |3 | +--+--+--+ 2 rows in set (0.00 sec) mysql SELECT col1, MAX(col2), col3 FROM test GROUP BY col1; +--+---+--+ | col1 | MAX(col2) | col3 | +--+---+--+ |1 | 2 |1 | +--+---+--+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: top one row
This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; +---+--+ + | Continent | Country | Population | +---+--+ + | Asia | China| 1277558000 | | North America | United States| 278357000 | | South America | Brazil | 170115000 | | Europe| Russian Federation | 146934000 | | Africa| Nigeria | 111506000 | | Oceania | Australia| 18886000 | | Antarctica| South Georgia and the South Sandwich Islands | 0 | +---+--+ It looks ugly but what you have to do is tie the data you want together and let the max work on the collection and then split it back out again in the display. So in your case SELECT col1, LEFT(MAX(CONCAT(LPAD(col2,10,'0'),col3)),10) + 0 AS col2, MID((MAX(CONCAT(LPAD(col2,10,'0'),col3)),11,50) + 0 As col3 FROM sample GROUP BY col1 -Original Message- From: Kemin Zhou [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 12:38 PM To: mysql@lists.mysql.com Subject: top one row I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- 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: QUERY (TOP)
Thanks Shawn! Peter Brawley [EMAIL PROTECTED] wrote:Seena, That data is for one date. To test Shawn's 2-stage query I'd need a data for several days. Without your tables and data, I tried Shawn's algorithm on a test database we use a lot, nwib, which is a MySQL port of the widely used (and copied) Microsoft Northwind database. It has customers, orders, orderdetails, payments etc, and it's populated. Your question, who are the top ten spammers per day? is logically equivalent, in nwib, to: what customers made the top ten number of orders for any given year? Translating Shawn's method to nwib.orders gives us: CREATE TABLE orderstats ( yr SMALLINT NOT NULL , rank INT AUTO_INCREMENT, customerID CHAR(5) NOT NULL, ordercount INT UNSIGNED NOT NULL, PRIMARY KEY(yr,rank) ); INSERT orderstats ( yr, customerID, ordercount ) SELECT YEAR(orderdate) AS yr, customerID, COUNT(customerID) AS ordercount FROM orders GROUP BY yr, customerID ORDER BY yr,ordercount DESC which, as Shawn says, auto-populates orderstats.rank for each group. After the above executes, SELECT * from orderstats WHERE rank = 10 gives the top ten ordering customers per year, as expected. So I am unclear why the algorithm does not work for your spam table. PB - Seena Blace wrote: Peter here u go.just a few.You may see sample report. 2005-05-05 | 1108529 | 0 | 1 |0 | 0 | | 2005-05-05 | 1108529 | 0 | 1 |0 | 0 | | 2005-05-05 | 1108529 | 0 | 1 |0 | 0 | | 2005-05-05 | 1108529 | 0 | 1 |0 | 0 | | 2005-05-05 | 1108529 | 0 | 1 |0 | 0 | | 2005-05-05 | 1108529 | 0 | 1 |0 | 0 | | 2005-05-05 | 14140 | 0 | 0 |0 | 0 | | 2005-05-05 | 46 | 1 | 0 |1 | 0 | | 2005-05-05 | 10378 | 0 | 0 |0 | 0 | | 2005-05-05 | 16068 | 0 | 0 |0 | 0 | | 2005-05-05 | 110085 | 0 | 1 |0 | 0 Peter Brawley [EMAIL PROTECTED] wrote: Seena, I'm curious how Shawn's elegant-looking solution fails. Would you mind sending me a small data sample so I can explore his solution? Thx. Peter Brawley - Seena Blace wrote: Shawan,Here you go.mysql select * from spam_stats- where rank =10 ;+-+-+--+---+---+---+| report_date | report_sender_domain_id | rank | processed | spam | suspected |+-+-+--+---+---+---+| 2005-04-07 | 46 |1 | 22054 | 19263 |13 || 2005-04-06 | 46 |2 | 20229 | 16998 |37 || 2005-04-08 | 46 |3 | 19493 | 16443 |24 || 2005-04-05 | 46 |4 | 19322 | 15921 | 158 || 2005-04-29 | 46 |5 | 19241 | 15804 | 8 || 2005-05-02 | 46 |6 | 47732 | 15287 |82 || 2005-05-04 | 46 |7 | 91907 | 14275 |25 || 2005-04-29 | 52 |8 | 15875 | 13422 | 4 || 2005-05-02 | 52 | 9 | 56530 | 13098 |31 || 2005-05-04 | 52 | 10 |129549 | 12566 |18 |+-+-+--+---+---+---+10 rows in set (0.00 sec)[EMAIL PROTECTED] wrote:Please try my solution before you tell me it's broken, OK? I know you want to see the top 10 spammers for EACH day. That's what I wrote for you. Please try my solution with your data and get back to me with the results and explain to me what's wrong so I can fix it.Thank you for your patience,Shawn GreenDatabase AdministratorUnimin Corporation - Spruce PineSeena Blace wrote on 05/16/2005 04:41:19 PM: Shawn,query SELECT * FROM spam_statsWHERE rank = 10;will return all rows which I don't want.I need datewise top 10 spam domain.means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send [EMAIL PROTECTED] wrote:Seena Blace wrote on 05/16/2005 11:13:48 AM: shawnI think 2nd query will return only 10 rows.I want out like followingsdate domain spam05/05/05 hotmail.com 12005/05/05 yahoo.com 11005/05/05 abc.com 9905/05/05 def.com 8005/05
Re: QUERY (TOP)
Shawan, Here you go. mysql select * from spam_stats - where rank =10 ; +-+-+--+---+---+---+ | report_date | report_sender_domain_id | rank | processed | spam | suspected | +-+-+--+---+---+---+ | 2005-04-07 | 46 |1 | 22054 | 19263 |13 | | 2005-04-06 | 46 |2 | 20229 | 16998 |37 | | 2005-04-08 | 46 |3 | 19493 | 16443 |24 | | 2005-04-05 | 46 |4 | 19322 | 15921 | 158 | | 2005-04-29 | 46 |5 | 19241 | 15804 | 8 | | 2005-05-02 | 46 |6 | 47732 | 15287 |82 | | 2005-05-04 | 46 |7 | 91907 | 14275 |25 | | 2005-04-29 | 52 |8 | 15875 | 13422 | 4 | | 2005-05-02 | 52 |9 | 56530 | 13098 |31 | | 2005-05-04 | 52 | 10 |129549 | 12566 |18 | +-+-+--+---+---+---+ 10 rows in set (0.00 sec) Thanks [EMAIL PROTECTED] wrote: Please try my solution before you tell me it's broken, OK? I know you want to see the top 10 spammers for EACH day. That's what I wrote for you. Please try my solution with your data and get back to me with the results and explain to me what's wrong so I can fix it. Thank you for your patience, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace wrote on 05/16/2005 04:41:19 PM: Shawn, query SELECT * FROM spam_stats WHERE rank = 10; will return all rows which I don't want. I need datewise top 10 spam domain. means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send earlier. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com 120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database
Re: QUERY (TOP)
Any suggestion pl? Seena Blace [EMAIL PROTECTED] wrote:hi, here is table description report1 +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id,processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-%d-%m') limit 10; Please suggest. thanks - Discover Yahoo! Find restaurants, movies, travel more fun for the weekend. Check it out! - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: QUERY (TOP)
Seena Blace [EMAIL PROTECTED] wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace [EMAIL PROTECTED] wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: QUERY (TOP)
shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - Yahoo! Mail Stay connected, organized, and protected. Take the tour
Re: QUERY (TOP)
Seena Blace [EMAIL PROTECTED] wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - OK, then what you want to do will take two steps and another table (unless you want to write application code) to do with MySQL; First step is to create a table where the intermediate results can be stored. What this table does is number each row for each day. Since it appears that you may want this information frequently (but only need to update it at the end of every day) I will make this a permanent table (not a temporary table). CREATE TABLE spam_stats ( report_date date not null, report_sender_domain_id int unsigned not null, rank int unsigned auto_increment, processed int unsigned, spam int unsigned, suspected int unsigned, PRIMARY KEY (report_date, rank), UNIQUE KEY (report_sender_domain_id, report_date), KEY (rank) ); The UNIQUE key ensures that the same spammer cannot have more than one entry per day while the PRIMARY KEY allows for a groupwize autonumber (each entry per day gets it's own number, rank, starting at 1). The last key is optional but will seriously speed up the returns for the report you wanted. Next we need to populate our new table with the spam report data (filled in from most spam to least spam per domain per day). INSERT spam_stats (report_date, report_sender_domain_id, processed, spam, suspected) SELECT date(`time`) ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date(`time`) ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date(`time`); What will happen is that the rank column will be automatically filled in for each day's spam statistics. Then to get your top ten list you just run: SELECT * FROM spam_stats WHERE
Re: QUERY (TOP)
Shawn, query SELECT * FROM spam_stats WHERE rank = 10; will return all rows which I don't want. I need datewise top 10 spam domain. means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send earlier. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com 120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - OK, then what you want to do will take two steps and another table (unless you want to write application code) to do with MySQL; First step is to create a table where the intermediate results can be stored. What this table does is number each row for each day. Since it appears that you may want this information frequently (but only need to update it at the end of every day) I will make this a permanent table (not a temporary table). CREATE TABLE spam_stats ( report_date date not null, report_sender_domain_id int unsigned not null, rank int unsigned auto_increment, processed int unsigned, spam int unsigned, suspected int unsigned, PRIMARY KEY (report_date, rank), UNIQUE KEY (report_sender_domain_id, report_date), KEY (rank) ); The UNIQUE key ensures that the same spammer cannot have more than one entry per day while the PRIMARY KEY allows for a groupwize autonumber (each entry per day gets it's own number, rank, starting at 1). The last key is optional but will seriously speed up the returns for the report you wanted. Next we need to populate our new table with the spam report data (filled in from most spam to least spam per domain per day). INSERT spam_stats (report_date, report_sender_domain_id, processed, spam, suspected) SELECT date(`time`) ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date(`time`) ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date(`time`); What will happen is that the rank column will be automatically filled in for each day's spam statistics. Then to get your top ten list you just run: SELECT * FROM spam_stats WHERE rank = 10; Update spam_stats at the end
Re: QUERY (TOP)
Please try my solution before you tell me it's broken, OK? I know you want to see the top 10 spammers for EACH day. That's what I wrote for you. Please try my solution with your data and get back to me with the results and explain to me what's wrong so I can fix it. Thank you for your patience, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace [EMAIL PROTECTED] wrote on 05/16/2005 04:41:19 PM: Shawn, query SELECT * FROM spam_stats WHERE rank = 10; will return all rows which I don't want. I need datewise top 10 spam domain. means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send earlier. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com 120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - OK, then what you want to do will take two steps and another table (unless you want to write application code) to do with MySQL; First step is to create a table where the intermediate results can be stored. What this table does is number each row for each day. Since it appears that you may want this information frequently (but only need to update it at the end of every day) I will make this a permanent table (not a temporary table). CREATE TABLE spam_stats ( report_date date not null, report_sender_domain_id int unsigned not null, rank int unsigned auto_increment, processed int unsigned, spam int unsigned, suspected int unsigned, PRIMARY KEY (report_date, rank), UNIQUE KEY (report_sender_domain_id, report_date), KEY (rank) ); The UNIQUE key ensures that the same spammer cannot have more than one entry per day while the PRIMARY KEY allows for a groupwize autonumber (each entry per day gets it's own number, rank, starting at 1). The last key is optional but will seriously speed up the returns for the report you wanted. Next we need to populate our new table with the spam report data (filled in from most spam
QUERY (TOP)
hi, here is table description report1 +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 || | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL || | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL || | report_ipaddress_id| int(10) unsigned | YES | MUL | NULL || | time| datetime | | MUL | -00-00 00:00:00 || | detected_spam | int(10) unsigned | | | 0 || | detected_virus | int(10) unsigned | | | 0 || | processed | int(10) unsigned | | | 0 || | allowed | int(10) unsigned | | | 0 || | suspected | int(10) unsigned | | | 0 || | blocked | int(10) unsigned | | | 0 || | spam| int(10) unsigned | | | 0 || | virus | int(10) unsigned | | | 0 || I WANT REPORT LIKE FOLLOWINGS datesenderprocessedspam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id,processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-%d-%m') limit 10; Please suggest. thanks - Discover Yahoo! Find restaurants, movies, travel more fun for the weekend. Check it out!
TOP N record
Hi, how to get top N records from table ? columns of table id date1 process wip worm I need output date1 process wip worm 5/5/05 5/6/05 5/7/05 thanks - Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. Learn more.
Re: TOP N record
SELECT date1, process, wip, worm FROM table WHERE field=value ORDER BY otherField DESC LIMIT N Seena Blace wrote: Hi, how to get top N records from table ? columns of table id date1 process wip worm I need output date1 process wip worm 5/5/05 5/6/05 5/7/05 thanks - Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. Learn more. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql top 2 rows for each group
I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql top 2 rows for each group
Hi, have you read my answer yesterday ? Mathias Selon Vivian Wang [EMAIL PROTECTED]: I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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 top 2 rows for each group
I did like this, look good, anyone can try it. set @a:=0; set @b:=0; update test set item=if([EMAIL PROTECTED], @a:[EMAIL PROTECTED], if(@b:=seqno, @a:=1, 0)); Vivian Wang wrote: I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql top 2 rows for each group
Vivian, Mathias was correct. I will be building on Mathias's answer by using your PK for your original data table (since you neglected to name it I will call it source_table) WITH THE ASSUMPTION that you cannot change your data structure and that you already have a PK on the table: Create a temporary table that counts how many records have each seqno (as Mathias said yesterday, this works because of a MySQL extension to auto_increment behavior and it only worked for him with a MyISAM table.) CREATE TEMPORARY TABLE tblSeqSort ( seqno int , seqcounter int auto_increment , id int , PRIMARY KEY (seqno, seqcounter) , UNIQUE (id) ); Then populate it in a particular order (the requirement of first two implies there is some kind of intrinsic order. I will sort by the ID value due to the lack of any other information about your data) INSERT tblSeqSort (seqno, id) SELECT seqno, id FROM source_table ORDER BY seqno, id; Then, just as Mathias demonstrated yesterday, you can get at just the first two records of each sequence by doing this SELECT st.id, st.seqno, other fields from source_table FROM source_table st INNER JOIN tblSeqSort sort ON sort.id = st.id AND sort.seqcounter =2; The drawback to this process is that you need to update your sequencing table each time you update your source_table or you could miss out on your most recently added source_table records. That can make this type of query time consuming and it may not be appropriate for many situations. It also requires more maintenance on your part. Mathias's solution (the simple solution) was to add the sequencing number to your original table. That way your data stays in sequence and the simple query is just what he proposed. This statement worked on a test table I have: ALTER TABLE source_table DROP PRIMARY KEY, ADD COLUMN seqcounter int auto_increment, ADD PRIMARY KEY (seqno, seqcounter); That makes your query: SELECT source_table field list FROM source_table WHERE seqcounter =2; Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 04/29/2005 09:31:23 AM: Hi, have you read my answer yesterday ? Mathias Selon Vivian Wang [EMAIL PROTECTED]: I don't have any PK in this table, but If you like one, I can add another column for PK, like, ++---+ | id | seqno | ++---+ | 1 | 00122 | | 2 | 00123 | | 3 | 00123 | | 4 | 00123 | | 5 | 00336 | | 6 | 00346 | | 7 | 00349 | | 8 | 00427 | | 9 | 00427 | | 10 | 00427 | ++---+--+ I searched on internet before, there is some solution like this, set @temp1:=0 set @temp2:=0 update test set item=if([EMAIL PROTECTED],@temp1:[EMAIL PROTECTED], @temp2:= seqno); but this logic is not correct, does anyone know how to solve this one? [EMAIL PROTECTED] wrote: Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql top 2 rows for each group
I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql top 2 rows for each group
[snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql top 2 rows for each group
Hi ,
The table must be myisam. Innodb refused my solution which is here :
Beatifull
auto_increment
mysql create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql
mysql
mysql insert into seqs(seqno) values('00122'),
-('00123'),
- ('00123'),
- ('00123'),
- ('00336'),
- ('00346'),
- ('00349'),
- ('00427'),
- ('00427'),
-('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql select * from seqs;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 | 3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 | 3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 |
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+
I like have
+---+--+
| seqno | item |
+---+--+
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 |3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 |3 |
+---+--+
Then I can have select * from test where item 3 to find all top 2 rows.
[/snip]
I think you want ...
SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2
--
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 top 2 rows for each group
Jay Blanchard [EMAIL PROTECTED] wrote on 04/28/2005 04:24:23 PM: [snip] I have question about how I can get top 2 rows for each group. like I have table test | seqno | +---+ | 00122 | | 00123 | | 00123 | | 00123 | | 00336 | | 00346 | | 00349 | | 00427 | | 00427 | | 00427 | +---+--+ I like have +---+--+ | seqno | item | +---+--+ | 00122 | 1 | | 00123 | 1 | | 00123 | 2 | | 00123 |3 | | 00336 | 1 | | 00346 | 1 | | 00349 | 1 | | 00427 | 1 | | 00427 | 2 | | 00427 |3 | +---+--+ Then I can have select * from test where item 3 to find all top 2 rows. [/snip] I think you want ... SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2 I think that will result in only two rows total, not two per group. Vivian? What is the PK for your table? What value or combination of values uniqely identifies each row of your source table? It can't be seqno as you already demonstrated that there are duplicate values in that column. I may have an idea but I need to know more about your data. Posting the results of SHOW CREATE TABLE xxx\G for your source table would be ideal. Thanks, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: mysql top 2 rows for each group
For your query, just a where clause :
mysql select * from seqs where id 3;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
+---++
8 rows in set (0.00 sec)
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: mathias fatene [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:52
To: 'Jay Blanchard'; 'Vivian Wang'; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
Hi ,
The table must be myisam. Innodb refused my solution which is here :
Beatifull
auto_increment
mysql create table seqs(seqno varchar(10) NOT NULL , id int
auto_increment, primary key (seqno,id)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql
mysql
mysql insert into seqs(seqno) values('00122'),
-('00123'),
- ('00123'),
- ('00123'),
- ('00336'),
- ('00346'),
- ('00349'),
- ('00427'),
- ('00427'),
-('00427');
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql select * from seqs;
+---++
| seqno | id |
+---++
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 | 3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 | 3 |
+---++
10 rows in set (0.00 sec)
- I like this type
of auto_increment
Best Regards
Mathias FATENE
Hope that helps
*This not an official mysql support answer
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: jeudi 28 avril 2005 22:24
To: Vivian Wang; mysql@lists.mysql.com
Subject: RE: mysql top 2 rows for each group
[snip]
I have question about how I can get top 2 rows for each group. like I
have table test
| seqno |
+---+
| 00122 |
| 00123 |
| 00123 |
| 00123 |
| 00336 |
| 00346 |
| 00349 |
| 00427 |
| 00427 |
| 00427 |
+---+--+
I like have
+---+--+
| seqno | item |
+---+--+
| 00122 | 1 |
| 00123 | 1 |
| 00123 | 2 |
| 00123 |3 |
| 00336 | 1 |
| 00346 | 1 |
| 00349 | 1 |
| 00427 | 1 |
| 00427 | 2 |
| 00427 |3 |
+---+--+
Then I can have select * from test where item 3 to find all top 2 rows.
[/snip]
I think you want ...
SELECT DISTINCT(seqno) FROM test ORDER BY seqno LIMIT 2
--
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]
Importing Tables on Top of Tables
If I revise a MySQL table and try to publish it online, I often get the error message, Table my_table already exists. So I have to delete the online version before I can import the revised version. Is there a way to just publish one table over another, as long as both have the same name? Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Importing Tables on Top of Tables
-Original Message- From: David Blomstrom Sent: Thursday, February 10, 2005 10:06 To: mysql@lists.mysql.com Subject: Importing Tables on Top of Tables If I revise a MySQL table and try to publish it online, I often get the error message, Table my_table already exists. So I have to delete the online version before I can import the revised version. This error comes from issuing a CREATE TABLE when a table with that name already exists. Is there a way to just publish one table over another, as long as both have the same name? There are two ways to do this. Issue a DROP TABLE IF EXISTS my_table, before the CREATE TABLE my_table, or you can issue TRUNCATE TABLE my_table and ditch the CREATE TABLE statement. Thanks. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Egor Egorov wrote: Bug filled: http://bugs.mysql.com/bug.php?id=5784 Thank you! Nice, thanks to you too ;-) regards, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Bug filled: http://bugs.mysql.com/bug.php?id=5784 Thank you! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Can you create a test case? I.e. a .sql file which is supposed to drop the table well but instead fails? This will help us determine if it's a bug and fix if it is. Thank you! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Hi, Egor Egorov wrote: Can you create a test case? I.e. a .sql file which is supposed to drop the table well but instead fails? This will help us determine if it's a bug and fix if it is. Thanks, this hit a pretty interesting nail for me: I can dump it, but I can't load the dump into the database again. I get: ERROR 1005 at line 28: Can't create table './test1/aktionen_produkte.frm' (errno: 150) The definition of the Table is: CREATE TABLE `aktionen_produkte` ( `ap_id` int(10) unsigned NOT NULL auto_increment, `ap_pr_id_produkt` int(10) unsigned NOT NULL default '0', `ap_ak_id_aktion` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`ap_id`), KEY `aktionen_produkte_FKIndex1` (`ap_ak_id_aktion`), KEY `aktionen_produkte_FKIndex2` (`ap_pr_id_produkt`), CONSTRAINT `aktionen_produkte_ibfk_1` FOREIGN KEY (`ap_ak_id_aktion`) REFERENCES `aktionen` (`ak_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `aktionen_produkte_ibfk_2` FOREIGN KEY (`ap_pr_id_produkt`) REFERENCES `produkte` (`pr_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; I guess I can see where the problem is: the order of creation of the tables is important, because if tries to create contraints to table which do not exist (yet) because they a further down in the dump file. Manually reordering doesn't seem very sexy to me. I read the mysqldump manpage but it doesn't tell me an option how to have mysqldump respect the ordering. Any ideas? thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Hi, Egor Egorov wrote: Can you create a test case? I.e. a .sql file which is supposed to drop the table well but instead fails? Nevermind my last post, I found the workaround to disable foregin_key_checks during import; interesting. Here is a small example: set foreign_key_checks=0; CREATE TABLE `produkt_kategorie` ( `pk_id` int(10) unsigned NOT NULL auto_increment, `pk_pt_id_typ` int(10) unsigned NOT NULL default '0', `pk_kategorie` varchar(255) default NULL, `pk_sortid` int(10) unsigned default NULL, PRIMARY KEY (`pk_id`), KEY `produk_kategorie_FKIndex1` (`pk_pt_id_typ`), CONSTRAINT `produkt_kategorie_ibfk_1` FOREIGN KEY (`pk_pt_id_typ`) REFERENCES `produktkategorie_typ` (`pt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; CREATE TABLE `produktkategorie_typ` ( `pt_id` int(10) unsigned NOT NULL auto_increment, `pt_name` varchar(255) default NULL, `pt_sortid` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`pt_id`) ) TYPE=InnoDB; set foreign_key_checks=1; When I now try to drop the table produktkategorie_typ I get the error: #1051 - Unknown table 'produktkategorie_typ' I think it's because of the constraint, but then the error message is missldeading. So basically I would need to disable the foreign_key_checks every time I drop a table which has constraints? I see the bigger picture now I guess, just didn't while trying to achive this with phpmyadmin. thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable top drop table, error 1051
Hi, I've a problem that I can't drop a certain table, always get back the error unknown table. Version: 4.0.21 (Debian Testing) Table-Type: InnoDB mysql show tables; [...] | produkt_kategorie| mysql drop table produkt_kategorie; ERROR 1051: Unknown table 'produkt_kategorie' The ownerships/permission seem right to me too: -rw-rw 1 mysql mysql 8670 Sep 21 11:23 produkt_kategorie.frm mysql describe produkt_kategorie; | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | pk_id| int(10) unsigned | | PRI | NULL| auto_increment | | pk_pt_id_typ | int(10) unsigned | | MUL | 0 || | pk_kategorie | varchar(255) | YES | | NULL|| | pk_sortid| int(10) unsigned | YES | | NULL|| pk_pt_id_typ is from a 1:n relation to the table produktkategorie_typ. I do the operation as user 'root' which has all access-rights. When I view the table in phpmyadmin (using 2.5.7pl1) I also see this additional information: InnoDB free: 44032 kB; (`pk_pt_id_typ`) REFER `produktkategorie_typ`(`pt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION I've used DbDesigner4 to design the table and then use the synchronisation feature to create the tables in the database. I'm using a 1:n relation from another table, produktkategorie_typ, to this table. I've created the Reference Definitions, but yet haven't assigned any actions (thus there's NO ACTION defined as seen above). I believe it has to do with the relation/reference definitions from InnoDB, so to me the message seems missleading. The only way for me to drop the table is to completely drop the database and recreate. thanks for any hints - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TOP
Shawn, Thanks for your help, I was wondering, if you can make any sense out of WHERE should I insert the TOP Commands, in the snippet below. Although this code is specific to our application, but maybe you can help me with this. Thanks, and I really appreciate your help. -Kamal. /* Build the Table Name(s) Microsoft SQL command - SELECT iv_sensor_names.name, iv_attack_0.name, iv_alert_type_0.name, iv_alert_severity_0.name, iv_categories_0.displayableName, iv_subcategories_0.display_name, iv_detection_0.displayableName, iv_direction_0.displayableName, iv_result_set_0.displayableName, iv_alert_0.creationTime FROM iv_alert iv_alert_0, iv_alert_severity iv_alert_severity_0, iv_alert_type iv_alert_type_0, iv_attack iv_attack_0, iv_categories iv_categories_0, iv_detection iv_detection_0, iv_direction iv_direction_0, iv_result_set iv_result_set_0, iv_sensor_names iv_sensor_names_0, iv_subcategories iv_subcategories_0 WHERE iv_alert_0.sensorId = iv_sensor_names_0.sensor_id AND iv_alert_0.alertType = iv_alert_type_0.id AND iv_alert_0.severity = iv_alert_severity_0.id AND iv_alert_0.attackIdRef = iv_attack_0.id AND iv_alert_0.categoryId = iv_categories_0.categoryId AND iv_alert_0.subCategoryId = iv_subcategories_0.idnum AND iv_alert_0.detectionMechanism = iv_detection_0.detectionMechanism AND iv_alert_0.resultSetValue = iv_result_set_0.resultSetValue AND iv_alert_0.direction = iv_direction_0.direction Table Joins - iv_alert LEFT JOIN iv_sensor_names ON iv_alert.sensorId = iv_sensor_names.sensor_id iv_alert LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id iv_alert LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id iv_alert LEFT JOIN iv_attack ON iv_attack.attackIdRef = iv_attack.id iv_alert LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId iv_alert LEFT JOIN iv_subcategories ON iv_alert.subCategoryId = iv_subcategories.idnum iv_alert LEFT JOIN iv_detection ON iv_alert.detectionMechanism = iv_detection.detectionMechanism iv_alert LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue iv_alert LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction Need LIMIT i_Max_Rows_To_Return */ BREAKPOINT() PRINTF(s_Table, iv_alert LEFT JOIN iv_sensor_names ON iv_alert.sensorid = iv_sensor_names.sensor_id ) APPEND(s_Table: LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id ) APPEND(s_Table: LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id ) APPEND(s_Table: LEFT JOIN iv_attack ON iv_alert.attackIdRef = iv_attack.id ) APPEND(s_Table: LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId ) APPEND(s_Table: LEFT JOIN iv_subcategories ON iv_alert.subCategoryId = iv_subcategories.idnum ) APPEND(s_Table: LEFT JOIN iv_detection ON iv_alert.detectionMechanism = iv_detection.detectionMechanism ) APPEND(s_Table: LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue ) APPEND(s_Table: LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction) /* Build the Where line */ CLEAR(s_Where) /* Logic goes here for Offset parsing */ PRINTF (s_Where, WHERE iv_alert.uuid %d ORDER BY iv_alert.uuid ASC, i_Offset ) /* Build the Column Names list */ PRINTF(s_Columns0, iv_alert.uuid, iv_sensor_names.name, iv_attack.name, iv_alert_type.name, iv_alert_severity.name, iv_categories.displayableName, ) PRINTF(s_Columns1, iv_subcategories.display_name, iv_detection.displayableName, iv_direction.displayableName, iv_result_set.displayableName, iv_alert.creationTime, ) PRINTF(s_Columns2, iv_alert.targetIPAddr, iv_alert.targetPort, iv_alert.sourceIPAddr, iv_alert.sourcePort, iv_alert.networkProtocolId ) PRINTF(s_Columns, %s%s%s, s_Columns0, s_Columns1, s_Columns2) BREAKPOINT() /* - DO NOT CHANGE THE LINES BELOW - */ LOOKUP(SelectDB_CheckStatus,eSecurity_Actions) SET(i_Record_Counter = 0) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 2:44 PM To: Kamal Ahmed Cc: [EMAIL PROTECTED] Subject: Re: TOP In MS SQL Server (T-SQL) you say SELECT TOP n In MySQL you use: SELECT LIMIT n (http://dev.mysql.com/doc/mysql/en/SELECT.html) There is no direct equivalent to SELECT TOP n PERCENT Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 07/28/2004 02:39:11 PM: Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- MySQL General Mailing List For list archives
RE: TOP
In MySQL, the LIMIT clause is usually the very LAST part of any query. I would put it here: /* Logic goes here for Offset parsing */ PRINTF (s_Where, WHERE iv_alert.uuid %d ORDER BY iv_alert.uuid ASC, i_Offset ) Right after the ASC. This should limit you to only 1 responses: ...ORDER BY iv_alert.uuid ASC LIMIT 1, i_Offset) If you read about it (http://dev.mysql.com/doc/mysql/en/SELECT.html) you see that it can help you with results paging as well ...ORDER BY iv_alert.uuid ASC LIMIT 1, 1000, i_Offset) That will give you the next 1000 records starting from record # 10001 (it's a zero-based number. First record = 0) . Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 07/29/2004 02:16:12 PM: Shawn, Thanks for your help, I was wondering, if you can make any sense out of WHERE should I insert the TOP Commands, in the snippet below. Although this code is specific to our application, but maybe you can help me with this. Thanks, and I really appreciate your help. -Kamal. /* Build the Table Name(s) Microsoft SQL command - SELECT iv_sensor_names.name, iv_attack_0.name, iv_alert_type_0.name, iv_alert_severity_0.name, iv_categories_0.displayableName, iv_subcategories_0.display_name, iv_detection_0.displayableName, iv_direction_0.displayableName, iv_result_set_0.displayableName, iv_alert_0.creationTime FROM iv_alert iv_alert_0, iv_alert_severity iv_alert_severity_0, iv_alert_type iv_alert_type_0, iv_attack iv_attack_0, iv_categories iv_categories_0, iv_detection iv_detection_0, iv_direction iv_direction_0, iv_result_set iv_result_set_0, iv_sensor_names iv_sensor_names_0, iv_subcategories iv_subcategories_0 WHERE iv_alert_0.sensorId = iv_sensor_names_0.sensor_id AND iv_alert_0.alertType = iv_alert_type_0.id AND iv_alert_0.severity = iv_alert_severity_0.id AND iv_alert_0.attackIdRef = iv_attack_0.id AND iv_alert_0.categoryId = iv_categories_0.categoryId AND iv_alert_0.subCategoryId = iv_subcategories_0.idnum AND iv_alert_0. detectionMechanism = iv_detection_0.detectionMechanism AND iv_alert_0.resultSetValue = iv_result_set_0.resultSetValue AND iv_alert_0.direction = iv_direction_0.direction Table Joins - iv_alert LEFT JOIN iv_sensor_names ON iv_alert.sensorId = iv_sensor_names.sensor_id iv_alert LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id iv_alert LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id iv_alert LEFT JOIN iv_attack ON iv_attack.attackIdRef = iv_attack.id iv_alert LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId iv_alert LEFT JOIN iv_subcategories ON iv_alert.subCategoryId = iv_subcategories.idnum iv_alert LEFT JOIN iv_detection ON iv_alert.detectionMechanism = iv_detection.detectionMechanism iv_alert LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue iv_alert LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction Need LIMIT i_Max_Rows_To_Return */ BREAKPOINT() PRINTF(s_Table, iv_alert LEFT JOIN iv_sensor_names ON iv_alert. sensorid = iv_sensor_names.sensor_id ) APPEND(s_Table: LEFT JOIN iv_alert_type ON iv_alert.alertType = iv_alert_type.id ) APPEND(s_Table: LEFT JOIN iv_alert_severity ON iv_alert.severity = iv_alert_severity.id ) APPEND(s_Table: LEFT JOIN iv_attack ON iv_alert.attackIdRef = iv_attack.id ) APPEND(s_Table: LEFT JOIN iv_categories ON iv_alert.categoryId = iv_categories.categoryId ) APPEND(s_Table: LEFT JOIN iv_subcategories ON iv_alert. subCategoryId = iv_subcategories.idnum ) APPEND(s_Table: LEFT JOIN iv_detection ON iv_alert. detectionMechanism = iv_detection.detectionMechanism ) APPEND(s_Table: LEFT JOIN iv_result_set ON iv_alert.resultSetValue = iv_result_set.resultSetValue ) APPEND(s_Table: LEFT JOIN iv_direction ON iv_alert.direction = iv_direction.direction) /* Build the Where line */ CLEAR(s_Where) /* Logic goes here for Offset parsing */ PRINTF (s_Where, WHERE iv_alert.uuid %d ORDER BY iv_alert.uuid ASC, i_Offset ) /* Build the Column Names list */ PRINTF(s_Columns0, iv_alert.uuid, iv_sensor_names.name, iv_attack. name, iv_alert_type.name, iv_alert_severity.name, iv_categories. displayableName, ) PRINTF(s_Columns1, iv_subcategories.display_name, iv_detection. displayableName, iv_direction.displayableName, iv_result_set. displayableName, iv_alert.creationTime, ) PRINTF(s_Columns2, iv_alert.targetIPAddr, iv_alert.targetPort, iv_alert.sourceIPAddr, iv_alert.sourcePort, iv_alert.networkProtocolId ) PRINTF(s_Columns, %s%s%s, s_Columns0, s_Columns1, s_Columns2) BREAKPOINT() /* - DO NOT CHANGE THE LINES BELOW - */ LOOKUP(SelectDB_CheckStatus,eSecurity_Actions) SET(i_Record_Counter = 0) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED
TOP
Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TOP
In MS SQL Server (T-SQL) you say SELECT TOP n In MySQL you use: SELECT LIMIT n (http://dev.mysql.com/doc/mysql/en/SELECT.html) There is no direct equivalent to SELECT TOP n PERCENT Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 07/28/2004 02:39:11 PM: Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TOP
TOP is a microsoft SQL extension. MySQL uses the LIMIT clause. for instance, the following is a rather typical top 10 sql query: select some_column, sum(another_column) total from some_table group by some_column order by total desc LIMIT 10 On Wed, 28 Jul 2004 14:39:11 -0400, Kamal Ahmed [EMAIL PROTECTED] wrote: Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- 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]
AW: TOP
Check out the LIMIT function in the documentation -Ursprüngliche Nachricht- Von: Kamal Ahmed [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 28. Juli 2004 20:39 An: [EMAIL PROTECTED] Betreff: TOP Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- 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]
Show Top 3 Matches in each Category
Hi all,
I am working on a search result query in which the user types a keyword and
the top 3 results for each category are displayed with 24 or so total results
on the page. The database is large (8 GB) and the table in which the fulltext
search occurs has 3 million rows.
Tables:
product
category
category_link
The category table contains many categories but I want to output only top
level general categories. Each product has a link to its general category in
the category_link table. The fulltext search runs fine if I don't attempt to
order and group the output by categories. Currently I have 1 query that gets
the top level categories (about 20 in all) then it loops through in PHP
executing a fulltext search on EACH of those results. This probably isn't the best
solution since it requires executing a fulltext search 20 different times and
in some cases (especially general words or multiword searches) takes much too
long. (anywhere from 2 - 45 seconds)
Is there a way to combine this into 1 FAST query? I'm using MySQL 4.0.17 and
PHP 4.3.4 so I don't think subqueries are an option... I suppose I could
upgrade if it was totally necessary.
The desired results for searching for Caffeine would look like this:
--
Search results for Caffeine
Drinks
- Caffeine Free Coke
- Caffeine Free Pepsi
- Jolt
Health Products
- NoDoz
- Generic Caffeine Tablets
- Caffeine Free Sleepytime Tea
Books
- Conquering Caffeine Dependence
- Understanding Caffeine : A Biobehavioral Analysis
- Over-the-Counter Drug Index 2004
Music
- DJ Micro - Caffeine: The Natural Stimulant
- Magnet - Caffeine Superstar
After each category I have a link to See all Matches in Category XYZ but
that query runs fine since the dataset to search is reduced in size to just that
particular category.
The simplified pseudo-code of what I am currently using is this:
GET TOP LEVEL CATEGORIES AND PLACE IN ARRAYS ( I left this code out)
// the resulting arrays might look like this:
$category_id = array(1,2,3);
$category_name = array(One,Two,Three);
for($i=0;$icount($category_id);$i++){
$query = SELECT P.product_id, P.product_name,...other stuff...FROM
product AS P, category AS C, category_link AS CL
INNER JOIN ... join necessary tables for images etc...
WHERE MATCH (P.product_name) AGAINST (' . $search_term . ') AND
CL.product_id = P.product_id AND CL.category_id = ' . $category_id[$i] . '
LIMIT 0, 3 ;
$result = mysql_query($query);
print() // output the 3 results
print(See all matches in . $category_name[$i]);
}
Thanks for any help!!!
- John
From the top! (primary/foreign keys)
OK, I've made some changes, and I'm still trying to figure out this foreign key stuff. This time, I'll focus on just two tables, named continents and nations. I put screen shots of their structure and some sample rows online at http://www.geoworld.org/try.gif It sounds like non-repeating numerals are generally preferred for use as primary keys, but letters can be used. I'd greatly prefer to use letters, because it's much more easier for me to match them with their respective countries and continents when working on related tables. But if this experiment fizzles out, too, I may abandon abbreviations and just use numerals for my primary keys. At any rate, I want to join the continents and nations tables via a shared column named CCode. In the continents table, it's designated a foreign key, and none of the codes repeat. In the nations table, some of the values do repeat - obviously, Canada and the U.S. both merit the abbreviation na (North America). If I understand correctly, I have to designate a column a unique index BEFORE I can make it a foreign key. I tried designating the CCode field on the table nations a unique index, without success. Is that because of the repeating values? I then tried to designate a column with non-repeating numerals a unique index, but that didn't work, either. I finally learned how to create a foreign key with phpMyAdmin, but that doesn't work, either. I put screenshots of my trials online at http://www.geoworld.org/fk.gif (The second table was then named nations2, but I've since renamed it to nations.) If the only solution is to make all my primary keys non-repeating numerals, then I'll go that route. If there are other problems, then I'd like to know if anyone on this list knows how to designate foreign keys with either MySQL-Front or SQLyog. I've used them as workarounds for other problems I have with phpMyAdmin, but their Help sections are worthless for foreign keys. Thanks. __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: From the top! (primary/foreign keys)
CREATE TABLE Continent ( CCODE VARCHAR(6) NOT NULL UNIQUE, CGROUP VARCHAR(255), TYPE VARCHAR(255), NAME VARCHAR(255), NUM_ID INT(4) NOT NULL UNIQUE, PRIMARY KEY (CCODE), TYPE = InnoDB); CREATE TABLE Nations ( NAME VARCHAR(255) NOT NULL UNIQUE, TYPE VARCHAR(255), NCODE VARCHAR(6), CCODE VARCHAR(6), PRIMARY KEY (NAME), INDEX (CCODE), FOREIGN KEY (CCODE) REFERENCES Continent(CCODE) ON DELETE RESTRICT ON UPDATE CASCADE) TYPE = InnoDB; I beleive that should get you what you are looking for ( I too am still a beginner so there are probably many more efficent ways of doing it). I noticed that you CCODE in the Nations table wasn't the same size as in the Continent table. Fixed that. Good luck! Respectfully, Ligaya Turmelle David Blomstrom [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] OK, I've made some changes, and I'm still trying to figure out this foreign key stuff. This time, I'll focus on just two tables, named continents and nations. I put screen shots of their structure and some sample rows online at http://www.geoworld.org/try.gif It sounds like non-repeating numerals are generally preferred for use as primary keys, but letters can be used. I'd greatly prefer to use letters, because it's much more easier for me to match them with their respective countries and continents when working on related tables. But if this experiment fizzles out, too, I may abandon abbreviations and just use numerals for my primary keys. At any rate, I want to join the continents and nations tables via a shared column named CCode. In the continents table, it's designated a foreign key, and none of the codes repeat. In the nations table, some of the values do repeat - obviously, Canada and the U.S. both merit the abbreviation na (North America). If I understand correctly, I have to designate a column a unique index BEFORE I can make it a foreign key. I tried designating the CCode field on the table nations a unique index, without success. Is that because of the repeating values? I then tried to designate a column with non-repeating numerals a unique index, but that didn't work, either. I finally learned how to create a foreign key with phpMyAdmin, but that doesn't work, either. I put screenshots of my trials online at http://www.geoworld.org/fk.gif (The second table was then named nations2, but I've since renamed it to nations.) If the only solution is to make all my primary keys non-repeating numerals, then I'll go that route. If there are other problems, then I'd like to know if anyone on this list knows how to designate foreign keys with either MySQL-Front or SQLyog. I've used them as workarounds for other problems I have with phpMyAdmin, but their Help sections are worthless for foreign keys. Thanks. __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: From the top! (primary/foreign keys)
BINGO! About the same time I got your message, I figured out how to create foreign keys with SQLyog. I followed your instructions in creating two brand new tables in phpMyAdmin, then linked the foreign key and primary key in SQLyog. I haven't tested it yet - there isn't even any data in the tables yet - but I see this message under Comments: InnoDB free: 3072 kB; [CCode] REFER world/continentsx[CCode] (I named my new experimental tables continentsx and nationsx. I should name one Ligaya in your honor! :) --- Ligaya Turmelle [EMAIL PROTECTED] wrote: CREATE TABLE Continent ( CCODE VARCHAR(6) NOT NULL UNIQUE, CGROUP VARCHAR(255), TYPE VARCHAR(255), NAME VARCHAR(255), NUM_ID INT(4) NOT NULL UNIQUE, PRIMARY KEY (CCODE), TYPE = InnoDB); CREATE TABLE Nations ( NAME VARCHAR(255) NOT NULL UNIQUE, TYPE VARCHAR(255), NCODE VARCHAR(6), CCODE VARCHAR(6), PRIMARY KEY (NAME), INDEX (CCODE), FOREIGN KEY (CCODE) REFERENCES Continent(CCODE) ON DELETE RESTRICT ON UPDATE CASCADE) TYPE = InnoDB; __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: From the top! (primary/foreign keys)
One more thing, though... I don't understand this restrict and cascade function. I looked for it when I created my table, but didn't see any mention of it. If I understand MySQL's documentation, I don't need to worry about it, because it's created by default every time you create a foreign key. Is that right? FOREIGN KEY (CCODE) REFERENCES Continent(CCODE) ON DELETE RESTRICT ON UPDATE CASCADE) TYPE = InnoDB; __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT TOP
What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
Yeah, put LIMIT 20 on the end. - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:12 PM Subject: SELECT TOP What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim -- 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: SELECT TOP
SELECT h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC limit 20; ^ You probably mean LIMIT to limit. On Mon, 2003-07-14 at 17:12, Jim McAtee wrote: What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
Be careful! Phil wanted the top 20 and the limit 20 will only return a random 20 records. Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Mon, 14 Jul 2003, Phil Bitis wrote: Yeah, put LIMIT 20 on the end. - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:12 PM Subject: SELECT TOP What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim -- 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: SELECT TOP
On Mon, 2003-07-14 at 17:44, Jake Johnson wrote: Be careful! Phil wanted the top 20 and the limit 20 will only return a random 20 records. Actually, LIMIT x will return the first x rows of a query, not random records. In fact, you can even combine ORDER BY and LIMIT if you want to get the first x rows of some order. http://www.mysql.com/doc/en/SELECT.html http://www.mysql.com/doc/en/LIMIT_optimisation.html gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim remove TOP 20 from front, add 'limit 20' to back. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
Sure Woody and Phil are right; just take out TOP 20 and put LIMIT 20 at the end of your SQL. If you care for the docs, the page is at http://www.mysql.com/doc/en/SELECT.html Peter Eternal Designs Phil Bitis wrote: Yeah, put LIMIT 20 on the end. - Original Message - From: Jim McAtee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:12 PM Subject: SELECT TOP What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
No kidding. I didn't know that. Thanks a lot! Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Mon, 14 Jul 2003, Gabriel Guzman wrote: On Mon, 2003-07-14 at 17:44, Jake Johnson wrote: Be careful! Phil wanted the top 20 and the limit 20 will only return a random 20 records. Actually, LIMIT x will return the first x rows of a query, not random records. In fact, you can even combine ORDER BY and LIMIT if you want to get the first x rows of some order. http://www.mysql.com/doc/en/SELECT.html http://www.mysql.com/doc/en/LIMIT_optimisation.html gabe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
Jake Johnson wrote: No kidding. I didn't know that. Thanks a lot! Regards, Jake Johnson [EMAIL PROTECTED] __ Plutoid - http://www.plutoid.com - Shop Plutoid for the best prices on Rims, Car Audio, and Performance Parts. On Mon, 14 Jul 2003, Gabriel Guzman wrote: On Mon, 2003-07-14 at 17:44, Jake Johnson wrote: Be careful! Phil wanted the top 20 and the limit 20 will only return a random 20 records. Actually, LIMIT x will return the first x rows of a query, not random records. In fact, you can even combine ORDER BY and LIMIT if you want to get the first x rows of some order. http://www.mysql.com/doc/en/SELECT.html http://www.mysql.com/doc/en/LIMIT_optimisation.html gabe. You can even specify an offset like this LIMIT 10, 20. This will skip the top 10 and give you the next 20. -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail)
Top MYSQL Sites
Hello Can somebody tell me what are the top sites ( except ofcourse mysql.com ) for general MySQL News, Tutorials etc. Insane - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query - Top Ten
Hello, I have a table with the following structure and values: CustomerIDScore 4 8 2 6 3 2 3 8 4 7 2 7 I would like to query for the top ten customer averages. Is this possible in a single query? The result will be something like: CustomerID Average Score 4 7.5 2 6.5 3 5 TIA. Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query - Top Ten
Hello, I have a table with the following structure and values: CustomerIDScore 4 8 2 6 3 2 3 8 4 7 2 7 I would like to query for the top ten customer averages. Is this possible in a single query? Try: select CustomerID, avg(score) as A from table_name group by CustomerID order by A desc limit 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query - Top Ten
SELECT ... ORDER BY score LIMIT 10 PB - - Original Message - From: Kevin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 29, 2002 1:11 PM Subject: Query - Top Ten Hello, I have a table with the following structure and values: CustomerIDScore 4 8 2 6 3 2 3 8 4 7 2 7 I would like to query for the top ten customer averages. Is this possible in a single query? The result will be something like: CustomerID Average Score 4 7.5 2 6.5 3 5 TIA. Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrade Q from 3.23 to 4 ie installing on top of 3.23
Hi there class myself slightly above noive tipping towards intermediate anyway i have for several months created db's under MySql 3.23 and utilised M$ Access with Myodbc been great so far . I have just download MySql4 to upgrade but i find that Mysql Doc page 2.5.1 Upgrading from Version 3,23 to Version 4 not so helfully in relation to installing on my homesation on Win98 ( Excuse my Language) I would like to insatll on top of my existing in C:\mysql so is there a quick guide install set up within the MySql Documentation somewhere as i have read the above whereby i believe once i have installed MySql 4 i thenn run mysql_fix_privilege_tables script etc etc.I would like some guidance or pointers as I have a complete Access DB that needs to be migrated to MySql hopefully MySql 4 as i need InnoDB for transactions and i also want to try out column encrypton for a credit_card_table. 1) I assume i install staringht into C:\mysql folder were my mysql 3.23 resides as iwas going to install intl a separate tmp folder ie C:\\windows\temp. 2) thus once i install within C:\mysql I run the script mysql mysql_fix_privilege_tables all my tables are currently MYISAM. Cheers Chuckie -- Regards Chuck Amadi ICT Dept Systems Programmer Rhaglenydd Systemau Adran ICT - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
