jmabbate

The MySQL syntax to update one table while joining to another is a bit different. It goes something like this: UPDATE staffemails e, staff s SET e.is_registered =\'0\' WHERE s.users_username = \'$user\' and e.staff_id = s.staff_id; BTW, this works in MySQL 4.0.4 or later. Joe

I think I didn\'t explain myself properly. Maybe an example will help. The data looks like this: Grp Item Count 1 abc 32 * 1 def 17 * 1 pqr 12 * 1 lmo 7 1 xyz 1 2 igh 12 * 2 lkj 9 * 2 uts 5 * 2 xwv 3 What I want is a query that will only display the rows that have an asterisk. In other words, group rows by grp number, ordering each row by descending count and pick only the top 3 rows in each group. Also, if a group has less than 3 rows at the top, I only want to show the first two (or one, whatever that is). The \"top 3\" could end up being \"top 5\" or something else. I could use a single query as you suggested, e.g., \"select grp, item, count order by grp, count desc\", but I think eventually that would become inefficient since the table will continue to grow. Also, my example is simplified: the real problem has two tables, so a join is necessary to get the grp and item (name) [join on item number], plus some rows are discarded based on columns in the first table, and the counts table also has a timestamp which perhaps will also be used to restrict rows. Joe

The syntax is ALTER TABLE table_name ADD COLUMN column_name INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (column_name); You\'ll find an example in the MySQL manual, section 6.5.4 under ALTER TABLE. Joe

Hi, I\'ve got a table with many different items which are categorized into eight groups. An auxiliary table has counts of hits against each item. I\'d like to select the top 3 items in each group, i.e., a total of 24 rows. I know I can do this in a loop, e.g., (in pseudo-php-mysql) for ($i = 1; $i <= 8; ++$i) select item, hitcount from hits where group = $i order by hits desc limit 0, 3; I was wondering whether there was some way to do this in a single SQL invocation instead of eight passes. I\'m sort of familiar with the GROUP BY and HAVING clauses, but I\'m not sure they can be used to achieve this. Any ideas? Joe